PostgreSQL and slow requests

nc-oc-filecache.md

dbnccl03=> \d+ oc_filecache
                                                               Table "nccl03.oc_filecache"
      Column      |          Type           | Collation | Nullable |                   Default                    | Storage  | Stats target | Description 
------------------+-------------------------+-----------+----------+----------------------------------------------+----------+--------------+-------------
 fileid           | bigint                  |           | not null | nextval('oc_filecache_fileid_seq'::regclass) | plain    |              | 
 storage          | bigint                  |           | not null | 0                                            | plain    |              | 
 path             | character varying(4000) |           |          | NULL::character varying                      | extended |              | 
 path_hash        | character varying(32)   |           | not null | ''::character varying                        | extended |              | 
 parent           | bigint                  |           | not null | 0                                            | plain    |              | 
 name             | character varying(250)  |           |          | NULL::character varying                      | extended |              | 
 mimetype         | bigint                  |           | not null | 0                                            | plain    |              | 
 mimepart         | bigint                  |           | not null | 0                                            | plain    |              | 
 size             | bigint                  |           | not null | 0                                            | plain    |              | 
 mtime            | bigint                  |           | not null | 0                                            | plain    |              | 
 storage_mtime    | bigint                  |           | not null | 0                                            | plain    |              | 
 encrypted        | integer                 |           | not null | 0                                            | plain    |              | 
 unencrypted_size | bigint                  |           | not null | 0                                            | plain    |              | 
 etag             | character varying(40)   |           |          | NULL::character varying                      | extended |              | 
 permissions      | integer                 |           |          | 0                                            | plain    |              | 
 checksum         | character varying(255)  |           |          | NULL::character varying                      | extended |              | 
Indexes:
    "oc_filecache_pkey" PRIMARY KEY, btree (fileid)
    "fs_storage_path_hash" UNIQUE, btree (storage, path_hash)
    "fs_id_storage_size" btree (fileid, storage, size)
    "fs_mtime" btree (mtime)
    "fs_parent_name_hash" btree (parent, name)
    "fs_size" btree (size)
    "fs_storage_mimepart" btree (storage, mimepart)
    "fs_storage_mimetype" btree (storage, mimetype)
    "fs_storage_size" btree (storage, size, fileid)
Access method: heap

dbnccl03=> EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT "file"."fileid", "storage", "path", "path_hash", "file"."parent", "name", "mimetype", "mimepart", "size", "mtime", "storage_mtime", "encrypted", "etag", "permissions", "checksum", "metadata_etag", "creation_time", "upload_time", "unencrypted_size" FROM "oc_filecache" "file" LEFT JOIN "oc_filecache_extended" "fe" ON "file"."fileid" = "fe"."fileid" WHERE ("mimetype" <> '2') AND (("storage" = 87) AND (("path_hash" = '9692aae50022f45f1098646939b287b1') OR ("path" LIKE 'files_versions/%')));
                                                                                  QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1001.13..401716.11 rows=3176 width=395) (actual time=30133.462..30175.950 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=79296 read=243034 dirtied=5
   ->  Nested Loop Left Join  (cost=1.13..400398.51 rows=1323 width=395) (actual time=30122.006..30122.006 rows=0 loops=3)
         Buffers: shared hit=79296 read=243034 dirtied=5
         ->  Parallel Index Scan using fs_storage_mimetype on oc_filecache file  (cost=0.56..396746.13 rows=1323 width=281) (actual time=30122.005..30122.005 rows=0 loops=3)
               Index Cond: (storage = 87)
               Filter: ((mimetype <> '2'::bigint) AND (((path_hash)::text = '9692aae50022f45f1098646939b287b1'::text) OR ((path)::text ~~ 'files_versions/%'::text)))
               Rows Removed by Filter: 129531
               Buffers: shared hit=79296 read=243034 dirtied=5
         ->  Index Scan using oc_filecache_extended_pkey on oc_filecache_extended fe  (cost=0.56..2.76 rows=1 width=122) (never executed)
               Index Cond: (fileid = file.fileid)
 Planning Time: 1.194 ms
 Execution Time: 30176.012 ms
(15 rows)