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 COUNT(*) FROM "oc_filecache" WHERE "storage" = 1;
                                                                                     QUERY PLAN                                                                             
         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------
 Finalize Aggregate  (cost=96155.70..96155.71 rows=1 width=8) (actual time=7252.695..7252.695 rows=1 loops=1)
   Buffers: shared hit=202760 read=211698 dirtied=4 written=163
   ->  Gather  (cost=96155.48..96155.69 rows=2 width=8) (actual time=7252.544..7313.959 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=202760 read=211698 dirtied=4 written=163
         ->  Partial Aggregate  (cost=95155.48..95155.49 rows=1 width=8) (actual time=7250.382..7250.382 rows=1 loops=3)
               Buffers: shared hit=202760 read=211698 dirtied=4 written=163
               ->  Parallel Index Only Scan using fs_storage_mimetype on oc_filecache  (cost=0.56..89540.85 rows=2245855 width=0) (actual time=0.923..7166.794 rows=1794680 
loops=3)
                     Index Cond: (storage = 1)
                     Heap Fetches: 856115
                     Buffers: shared hit=202760 read=211698 dirtied=4 written=163
 Planning Time: 0.100 ms
 Execution Time: 7314.004 ms
(14 rows)

dbnccl03=> EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT COUNT(*) FROM "oc_filecache" WHERE "storage" = 46;
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=216.75..216.75 rows=1 width=8) (actual time=32.400..32.400 rows=1 loops=1)
   Buffers: shared hit=549 read=141
   ->  Index Only Scan using fs_storage_mimetype on oc_filecache  (cost=0.56..195.22 rows=8609 width=0) (actual time=1.143..32.056 rows=7421 loops=1)
         Index Cond: (storage = 46)
         Heap Fetches: 1079
         Buffers: shared hit=549 read=141
 Planning Time: 0.087 ms
 Execution Time: 32.421 ms
(8 rows)

dbnccl03=> 
dbnccl03=> 
dbnccl03=> \di+ fs_storage_mimetype
                                  List of relations
 Schema |        Name         | Type  | Owner  |    Table     |  Size   | Description 
--------+---------------------+-------+--------+--------------+---------+-------------
 nccl03 | fs_storage_mimetype | index | nccl03 | oc_filecache | 1820 MB | 
(1 row)

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)