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.
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
