very slow preview load and HTTP500 - Postgresql database index missing for storage+path_hash

Hi,

i just want to share my experience and fix.

I installed the plugin for the first time on a uptodate nc docker instance with a separate pg container.

# occ status
  - installed: true
  - version: 28.0.1.1
  - versionstring: 28.0.1
  - edition: 
  - maintenance: false
  - needsDbUpgrade: false
  - productname: Nextcloud
  - extendedSupport: false

# occ app:list | grep preview
  - previewgenerator: 5.4.0

PSQL># SELECT version();
                                                       version                                                       
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.5 (Debian 15.5-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

The pageload was very slow. first preview request were answered, some got HTTP 500.

After digging into the DB, I noticed that the queries were extremely slow. which leaded to this pg log lines (and many more):

2024-01-13 15:07:25.717 UTC [57479] LOG:  duration: 5389.743 ms  execute <unnamed>: SELECT "fileid" FROM "oc_filecache" WHERE ("storage" = $1) AND ("path_hash" = $2)
2024-01-13 15:07:25.717 UTC [57479] DETAIL:  parameters: $1 = '1', $2 = 'c164c3a63de651e7e52bac30733dee84'
2024-01-13 15:07:27.572 UTC [57526] LOG:  duration: 6045.899 ms  execute <unnamed>: SELECT "fileid" FROM "oc_filecache" WHERE ("storage" = $1) AND ("path_hash" = $2)
2024-01-13 15:07:27.572 UTC [57526] DETAIL:  parameters: $1 = '1', $2 = 'd41d8cd98f00b204e9800998ecf8427e'
2024-01-13 15:07:27.712 UTC [57523] LOG:  duration: 6238.780 ms  execute <unnamed>: SELECT "fileid" FROM "oc_filecache" WHERE ("storage" = $1) AND ("path_hash" = $2)
2024-01-13 15:07:27.712 UTC [57523] DETAIL:  parameters: $1 = '1', $2 = 'd41d8cd98f00b204e9800998ecf8427e'
2024-01-13 15:07:29.040 UTC [57478] LOG:  duration: 6433.436 ms  execute <unnamed>: SELECT "fileid" FROM "oc_filecache" WHERE ("storage" = $1) AND ("path_hash" = $2)
2024-01-13 15:07:29.040 UTC [57478] DETAIL:  parameters: $1 = '1', $2 = 'c164c3a63de651e7e52bac30733dee84'

so i digged into the index of the table oc_filecache.

owncloud=# select * from pg_indexes where tablename = 'oc_filecache';
 schemaname |  tablename   |       indexname        | tablespace |                                          indexdef
------------+--------------+------------------------+------------+--------------------------------------------------------------------------------------------
 public     | oc_filecache | fs_id_storage_size     |            | CREATE INDEX fs_id_storage_size ON public.oc_filecache USING btree (fileid, storage, size)
 public     | oc_filecache | fs_mtime               |            | CREATE INDEX fs_mtime ON public.oc_filecache USING btree (mtime)
 public     | oc_filecache | fs_parent              |            | CREATE INDEX fs_parent ON public.oc_filecache USING btree (parent)
 public     | oc_filecache | fs_parent_name_hash    |            | CREATE INDEX fs_parent_name_hash ON public.oc_filecache USING btree (parent, name)
 public     | oc_filecache | fs_size                |            | CREATE INDEX fs_size ON public.oc_filecache USING btree (size)
 public     | oc_filecache | fs_storage_mimepart    |            | CREATE INDEX fs_storage_mimepart ON public.oc_filecache USING btree (storage, mimepart)
 public     | oc_filecache | fs_storage_mimetype    |            | CREATE INDEX fs_storage_mimetype ON public.oc_filecache USING btree (storage, mimetype)
 public     | oc_filecache | fs_storage_size        |            | CREATE INDEX fs_storage_size ON public.oc_filecache USING btree (storage, size, fileid)
 public     | oc_filecache | fs_storage_path_prefix |            | CREATE INDEX fs_storage_path_prefix ON public.oc_filecache USING btree (storage, path)
(9 rows)

there is no index for the problematic queries which indexes storage and path_hash.

so i added the missing index:

owncloud=# CREATE INDEX fs_storage_path_hash ON public.oc_filecache USING btree (storage, path_hash);
CREATE INDEX

owncloud=# select * from pg_indexes where tablename = 'oc_filecache';
 schemaname |  tablename   |       indexname        | tablespace |                                          indexdef                                          
------------+--------------+------------------------+------------+--------------------------------------------------------------------------------------------
 public     | oc_filecache | fs_id_storage_size     |            | CREATE INDEX fs_id_storage_size ON public.oc_filecache USING btree (fileid, storage, size)
 public     | oc_filecache | fs_mtime               |            | CREATE INDEX fs_mtime ON public.oc_filecache USING btree (mtime)
 public     | oc_filecache | fs_parent              |            | CREATE INDEX fs_parent ON public.oc_filecache USING btree (parent)
 public     | oc_filecache | fs_parent_name_hash    |            | CREATE INDEX fs_parent_name_hash ON public.oc_filecache USING btree (parent, name)
 public     | oc_filecache | fs_size                |            | CREATE INDEX fs_size ON public.oc_filecache USING btree (size)
 public     | oc_filecache | fs_storage_mimepart    |            | CREATE INDEX fs_storage_mimepart ON public.oc_filecache USING btree (storage, mimepart)
 public     | oc_filecache | fs_storage_mimetype    |            | CREATE INDEX fs_storage_mimetype ON public.oc_filecache USING btree (storage, mimetype)
 public     | oc_filecache | fs_storage_size        |            | CREATE INDEX fs_storage_size ON public.oc_filecache USING btree (storage, size, fileid)
 public     | oc_filecache | fs_storage_path_prefix |            | CREATE INDEX fs_storage_path_prefix ON public.oc_filecache USING btree (storage, path)
 public     | oc_filecache | fs_storage_path_hash   |            | CREATE INDEX fs_storage_path_hash ON public.oc_filecache USING btree (storage, path_hash)
(10 rows)

...and got a significantly increase of the preview/page load speed.

I am not into the nc-plugin code to check if this is individual problem of my installation / migration paths. Or if there is something missing for auto-creation of this index.

So can someone verify this, please?

Start by tracing the SELECT on oc_filecache using storage and path_hash, then inspect the previewgenerator database schema or migration path for index creation. Compare installations with and without fs_storage_path_hash and verify that the preview queries no longer take several seconds or return HTTP 500 errors.