Re: Slow Query - PostgreSQL 9.2

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.