Postgres Performance Improvement of fulltextsearch file provider for DB operations via appropriate indexing of oc_filecache

How to use GitHub

• Please use the 👍 reaction to show that you are interested into the same feature.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

The implementation of this improvement is documented in https://github.com/nextcloud/fulltextsearch/issues/850

Since this needs to be implemented into the Postgres DB initialization I also post it in here.

Having an ILIKE SQL operation on the "name" field of table "oc_filecache" while the default index is a compound BTREE index over the fields "parent" and "name" slows down responsiveness of a query massively.

The relevant default indexes:

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

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);

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

Start by reading the implementation described in nextcloud/fulltextsearch issue 850 and locate the PostgreSQL database initialization in the server repository. The work is done when the pg_trgm extension and the proposed GIN trigram indexes for oc_filecache.name and oc_filecache.path are added appropriately and the database setup remains valid.