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
Created
February 5, 2023 12:20
-
-
Save flaf/7548f4568a3e275eead950b46ac9a9ff to your computer and use it in GitHub Desktop.
PostgreSQL and slow requests
Author
flaf
commented
Feb 5, 2023
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)
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment