Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
cue108 opened this issue Jul 13, 2024 · 0 comments

Comments

@cue108
Copy link

cue108 commented Jul 13, 2024

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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant