Massive performance improvement on file provider with DB operation under Postgres solving my own issue #799

Analysing Postgres performance pointed out a query like this:

An Excerpt!

SELECT "file"."fileid", "storage", "path", "path_hash", "file"."parent", "file"."name", "mimetype", "mimepart", "size", "mtime", "storage_mtime", "encrypted", "etag", "file"."permissions", "checksum", "unencrypted_size", "meta"."json" AS "meta_json", "meta"."sync_token" AS "meta_sync_token" 
FROM "oc_filecache" "file" 
LEFT JOIN "oc_files_metadata" "meta" ON "file"."fileid" = "meta"."file_id" 
WHERE ("file"."name" ILIKE $1) AND ((("storage" = $2) AND (("path" = $3) ........

The relevant index for the ILIKE operation is a compound BTREE index:

CREATE INDEX fs_parent_name_hash ON public.oc_filecache USING btree
(
   parent,
   name
)
;

The relevant index for a LIKE operation in a similar query is a compound BTREE index too:

CREATE INDEX fs_storage_path_prefix ON public.oc_filecache USING btree
(
   storage,
   path
)
;

add the following indexes

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX fs_name_gin_trgm ON public.oc_filecache USING GIN (name gin_trgm_ops);

and

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX fs_path_gin_trgm ON public.oc_filecache USING GIN (path gin_trgm_ops);

My oc_filecache table has over a million entries and the Postgres instance is running on a spare laptop.
The query response is now nearly instantly and on par with the elastic_search file provider response.

May it help those who care!

The issue names no repository files or tests; start by locating the file-provider query and the PostgreSQL schema or migration entry points. Reproduce the reported query plan, then assess the proposed pg_trgm indexes and verify that the file-provider operation improves without breaking database setup or existing tests.