Обсуждение: Slow Query - PostgreSQL 9.2
I've got a slow query.. I'd like to make it faster.. Make add an index?
Query:
What could I do to make it faster? Thank you.
Query:
SELECT j.clientid AS client_id, ni.segment_index AS note_id, f.inode_id AS file_id, f.node_full_path AS filename, f.last_changed AS date_created, f.file_data AS main_binary, medium.inode_id AS medium_id, medium.file_data AS medium_binary, thumbnail.inode_id AS thumbnail_id, thumbnail.file_data AS thumbnail_binary
FROM gorfs.nodes AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'main.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
INNER JOIN gorfs.inode_segments AS fn ON fn.st_ino_target = fi.st_ino
INNER JOIN gorfs.inode_segments AS ni ON ni.st_ino_target = fn.st_ino
INNER JOIN public.ja_notes AS n ON n.id = CAST(ni.segment_index AS INTEGER)
INNER JOIN public.ja_jobs AS j ON j.id = n.jobid
LEFT JOIN (SELECT f.inode_id, f.file_data, fi.st_ino FROM gorfs.nodes AS f INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino AND mv.segment_index = 'medium.with_name' INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino) AS medium ON medium.st_ino = fn.st_ino_target
LEFT JOIN (SELECT f.inode_id, f.file_data, fi.st_ino FROM gorfs.nodes AS f INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino AND mv.segment_index = 'thumbnail.with_name' INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino) AS thumbnail ON thumbnail.st_ino = fn.st_ino_target
WHERE f.file_data IS NOT NULL AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed + '24 months' :: INTERVAL)) LIMIT 100;"Limit (cost=1556.99..1336437.30 rows=100 width=186) (actual time=94987.261..94987.261 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=1556.99..579473097.84 rows=43410 width=186) (actual time=94987.257..94987.257 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=1038.00..483232645.16 rows=43410 width=154) (actual time=94987.255..94987.255 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..409353299.84 rows=43410 width=114) (actual time=94987.252..94987.252 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..409094090.84 rows=43410 width=114) (actual time=94987.250..94987.250 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..408681283.16 rows=43410 width=106) (actual time=94987.247..94987.247 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..407691740.11 rows=64840 width=106) (actual time=94987.244..94987.244 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..406213713.19 rows=96848 width=98) (actual time=94987.241..94987.241 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..403641904.83 rows=191391 width=106) (actual time=94987.239..94987.239 rows=0 loops=1)"
" Join Filter: (CASE WHEN ("f"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"
" -> Nested Loop (cost=519.00..349935407.61 rows=287309 width=36) (actual time=94987.236..94987.236 rows=0 loops=1)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("f"."bits")::"bit")"
" -> Nested Loop (cost=0.00..349819245.82 rows=287309 width=41) (actual time=94987.233..94987.233 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..343269999.71 rows=429140 width=41) (actual time=94987.231..94987.231 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..206165095.07 rows=8982354 width=41) (actual time=94987.228..94987.228 rows=0 loops=1)"
" -> Seq Scan on "inodes" "t" (cost=0.00..1411147.24 rows=13416537 width=29) (actual time=94987.224..94987.224 rows=0 loops=1)"
" Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) > (("st_ctime")::timestamp without time zone + '2 years'::interval))"
" Rows Removed by Filter: 40683998"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"
" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"
" -> Materialize (cost=519.00..519.97 rows=23 width=36) (never executed)"
" -> Subquery Scan on "f" (cost=519.00..519.86 rows=23 width=36) (never executed)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 6"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=78) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"
" SubPlan 4"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"
" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"
" Index Cond: ("id" = ("ni"."segment_index")::integer)"
" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"
" Index Cond: ("id" = "n"."jobid")"
" Heap Fetches: 0"
" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"
" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"
" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"
" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"
" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"
" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"
" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"
" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 9"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 2"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"
" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"
" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"
" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"
" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"
" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 12"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 3"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" SubPlan 1"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
"Total runtime: 94989.208 ms"What could I do to make it faster? Thank you.
1.
use the word WITH
with xxx as ( select ...)
select a,b,c
from tab
left join xxx as medium on (...)
left join xxx as thumbnail on (...)
2.
in the explain plan you find many nested loops,
sometimes a hash join is faster.
3.
if it is possible, create a temporary table and don't use so many
sub-selects.
On 11.01.2016 05:30, Saulo Merlo wrote:
> I've got a slow query.. I'd like to make it faster.. Make add an index?
> *Query:*
>
> |SELECTj.clientid ASclient_id,ni.segment_index ASnote_id,f.inode_id
> ASfile_id,f.node_full_path ASfilename,f.last_changed
> ASdate_created,f.file_data ASmain_binary,medium.inode_id
> ASmedium_id,medium.file_data ASmedium_binary,thumbnail.inode_id
> ASthumbnail_id,thumbnail.file_data ASthumbnail_binary FROMgorfs.nodes
> ASf INNERJOINgorfs.inode_segments ASfd ONfd.st_ino_target =f.inode_id
> INNERJOINgorfs.inode_segments ASmv ONmv.st_ino_target =fd.st_ino
> ANDmv.segment_index ='main.with_name'INNERJOINgorfs.inode_segments ASfi
> ONfi.st_ino_target =mv.st_ino INNERJOINgorfs.inode_segments ASfn
> ONfn.st_ino_target =fi.st_ino INNERJOINgorfs.inode_segments ASni
> ONni.st_ino_target =fn.st_ino INNERJOINpublic.ja_notes ASn ONn.id
> =CAST(ni.segment_index ASINTEGER)INNERJOINpublic.ja_jobs ASj ONj.id
> =n.jobid LEFTJOIN(SELECTf.inode_id,f.file_data,fi.st_ino FROMgorfs.nodes
> ASf INNERJOINgorfs.inode_segments ASfd ONfd.st_ino_target =f.inode_id
> INNERJOINgorfs.inode_segments ASmv ONmv.st_ino_target =fd.st_ino
> ANDmv.segment_index ='medium.with_name'INNERJOINgorfs.inode_segments
> ASfi ONfi.st_ino_target =mv.st_ino)ASmedium ONmedium.st_ino
> =fn.st_ino_target LEFTJOIN(SELECTf.inode_id,f.file_data,fi.st_ino
> FROMgorfs.nodes ASf INNERJOINgorfs.inode_segments ASfd
> ONfd.st_ino_target =f.inode_id INNERJOINgorfs.inode_segments ASmv
> ONmv.st_ino_target =fd.st_ino ANDmv.segment_index
> ='thumbnail.with_name'INNERJOINgorfs.inode_segments ASfi
> ONfi.st_ino_target =mv.st_ino)ASthumbnail ONthumbnail.st_ino
> =fn.st_ino_target WHEREf.file_data
> ISNOTNULLAND((transaction_timestamp()AT TIME ZONE 'UTC')>(f.last_changed
> +'24 months'::INTERVAL))LIMIT 100;|
>
> *EXPLAIN ANALYZE:*
>
> |"Limit (cost=1556.99..1336437.30 rows=100 width=186) (actual
> time=94987.261..94987.261 rows=0 loops=1)"" -> Nested Loop Left Join
> (cost=1556.99..579473097.84 rows=43410 width=186) (actual
> time=94987.257..94987.257 rows=0 loops=1)"" -> Nested Loop Left Join
> (cost=1038.00..483232645.16 rows=43410 width=154) (actual
> time=94987.255..94987.255 rows=0 loops=1)"" -> Nested Loop
> (cost=519.00..409353299.84 rows=43410 width=114) (actual
> time=94987.252..94987.252 rows=0 loops=1)"" -> Nested Loop
> (cost=519.00..409094090.84 rows=43410 width=114) (actual
> time=94987.250..94987.250 rows=0 loops=1)"" -> Nested Loop
> (cost=519.00..408681283.16 rows=43410 width=106) (actual
> time=94987.247..94987.247 rows=0 loops=1)"" -> Nested Loop
> (cost=519.00..407691740.11 rows=64840 width=106) (actual
> time=94987.244..94987.244 rows=0 loops=1)"" -> Nested Loop
> (cost=519.00..406213713.19 rows=96848 width=98) (actual
> time=94987.241..94987.241 rows=0 loops=1)"" -> Nested Loop
> (cost=519.00..403641904.83 rows=191391 width=106) (actual
> time=94987.239..94987.239 rows=0 loops=1)"" Join Filter: (CASE WHEN
> ("f"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN
> (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"" -> Nested Loop
> (cost=519.00..349935407.61 rows=287309 width=36) (actual
> time=94987.236..94987.236 rows=0 loops=1)"" Join Filter:
> ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") =
> ("f"."bits")::"bit")"" -> Nested Loop (cost=0.00..349819245.82
> rows=287309 width=41) (actual time=94987.233..94987.233 rows=0
> loops=1)"" -> Nested Loop (cost=0.00..343269999.71 rows=429140 width=41)
> (actual time=94987.231..94987.231 rows=0 loops=1)"" -> Nested Loop
> (cost=0.00..206165095.07 rows=8982354 width=41) (actual
> time=94987.228..94987.228 rows=0 loops=1)"" -> Seq Scan on "inodes" "t"
> (cost=0.00..1411147.24 rows=13416537 width=29) (actual
> time=94987.224..94987.224 rows=0 loops=1)"" Filter:
> ("timezone"('UTC'::"text", "transaction_timestamp"()) >
> (("st_ctime")::timestamp without time zone + '2 years'::interval))""
> Rows Removed by Filter: 40683998"" -> Index Scan using
> "ix_inode_segments_st_ino_targets" on "inode_segments" "fd"
> (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond:
> (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"" -> Index Scan
> using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv"
> (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond:
> (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"" Filter:
> (("segment_index")::"text" = 'main.with_name'::"text")"" -> Index Scan
> using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi"
> (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond:
> (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"" -> Materialize
> (cost=519.00..519.97 rows=23 width=36) (never executed)"" -> Subquery
> Scan on "f" (cost=519.00..519.86 rows=23 width=36) (never executed)"" ->
> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23
> width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*"
> (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE
> stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71
> rows=23 width=68) (never executed)"" SubPlan 6"" -> Aggregate
> (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan
> on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000
> width=32) (never executed)"" -> Index Scan using
> "ix_inode_segments_st_ino_targets" on "inode_segments" "p"
> (cost=0.00..15.25 rows=1 width=78) (never executed)"" Index Cond:
> (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"" SubPlan 4"" ->
> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" ->
> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs"
> (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond:
> (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan
> using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8)
> (never executed)"" Index Cond: (("st_ino")::bigint =
> ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR
> ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") =
> B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using
> "ix_inode_segments_st_ino_targets" on "inode_segments" "fn"
> (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond:
> (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"" -> Index Scan
> using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni"
> (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond:
> (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"" -> Index Scan
> using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1
> width=16) (never executed)"" Index Cond: ("id" =
> ("ni"."segment_index")::integer)"" -> Index Only Scan using
> "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1
> width=16) (never executed)"" Index Cond: ("id" = "n"."jobid")"" Heap
> Fetches: 0"" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48)
> (never executed)"" Join Filter: (("sb"."bits")::"bit" =
> (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))""
> -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)""
> -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)""
> -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)""
> -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)""
> -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" ->
> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi"
> (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond:
> (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan
> using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94
> rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint =
> ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" =
> 'medium.with_name'::"text"))"" -> Index Scan using
> "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71
> rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint =
> ("mv"."st_ino_target")::bigint)"" -> Index Scan using
> "ix_inode_segments_st_ino_targets" on "inode_segments" "p"
> (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond:
> (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index
> Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8)
> (never executed)"" Index Cond: (("st_ino")::bigint =
> ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR
> ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") =
> B'00000000000000000100000000000000'::"bit"))"" -> CTE Scan on
> "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never
> executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29
> rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan
> on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)""
> SubPlan 9"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never
> executed)"" -> Function Scan on "regexp_split_to_table" "digits"
> (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 2"" ->
> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" ->
> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs"
> (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond:
> (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan
> using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21)
> (never executed)"" Index Cond: (("st_ino")::bigint =
> ("p"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..1702.00
> rows=1 width=48) (never executed)"" Join Filter:
> ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") =
> ("sb"."bits")::"bit")"" -> Nested Loop (cost=0.00..1181.79 rows=1
> width=33) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1
> width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1
> width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1
> width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1
> width=16) (never executed)"" -> Index Scan using
> "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61
> rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint =
> ("fn"."st_ino_target")::bigint)"" -> Index Scan using
> "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1
> width=16) (never executed)"" Index Cond: ((("st_ino")::bigint =
> ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" =
> 'thumbnail.with_name'::"text"))"" -> Index Scan using
> "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71
> rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint =
> ("mv"."st_ino_target")::bigint)"" -> Index Scan using
> "ix_inode_segments_st_ino_targets" on "inode_segments" "p"
> (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond:
> (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index
> Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8)
> (never executed)"" Index Cond: (("st_ino")::bigint =
> ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR
> ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") =
> B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using
> "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never
> executed)"" Index Cond: (("st_ino")::bigint =
> ("p"."st_ino_target")::bigint)"" -> CTE Scan on "stat_h_with_bits" "sb"
> (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h""
> -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never
> executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s"
> (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 12"" ->
> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" ->
> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01
> rows=1000 width=32) (never executed)"" SubPlan 3"" -> Aggregate
> (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan
> using "ix_inode_segments_st_inos" on "inode_segments" "fs"
> (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond:
> (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" SubPlan 1"" ->
> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" ->
> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs"
> (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond:
> (("st_ino")::bigint = ("p"."st_ino_target")::bigint)""Total runtime:
> 94989.208 ms"|
>
>
>
>
>
> What could I do to make it faster? Thank you.