pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow Query - PostgreSQL 9.2
6+ messages / 3 participants
[nested] [flat]
* Slow Query - PostgreSQL 9.2
@ 2016-01-11 04:30 Saulo Merlo <smerlo50@outlook.com>
0 siblings, 1 reply; 6+ messages in thread
From: Saulo Merlo @ 2016-01-11 04:30 UTC (permalink / raw)
To: pgsql-admin
I've got a slow query.. I'd like to make it faster.. Make add an index?
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;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.
=
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow Query - PostgreSQL 9.2
@ 2016-01-11 06:08 Franz Timmer <ml@ft-c.de>
parent: Saulo Merlo <smerlo50@outlook.com>
0 siblings, 0 replies; 6+ messages in thread
From: Franz Timmer @ 2016-01-11 06:08 UTC (permalink / raw)
To: pgsql-admin
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Slow Query - PostgreSQL 9.2
@ 2016-01-12 08:58 Saulo Merlo <smerlo50@outlook.com>
0 siblings, 1 reply; 6+ messages in thread
From: Saulo Merlo @ 2016-01-12 08:58 UTC (permalink / raw)
To: pgsql-admin
I've been trying to make this Query faster, but I had no success.
Do you guys have an idea about what else I can do?
I'll post below what I have done.
Thank you.
This can either be nfs_file_path or nfs_migration_date (both new columns). Adding an index on either of these columns and using them in the clause should improve things greatly.
QUERY:
SELECT
main.inode_id AS file_id,
main.file_data AS main_binary,
main.node_full_path AS filename,
main.last_modified AS date_created,
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 main
INNER JOIN
gorfs.inode_segments AS iseg ON iseg.st_ino = main.parent_inode_id
AND main.relative_path = 'main'
AND main.object_type = 'S_IFREG'
AND iseg.nfs_migration_date IS NULL
AND (main.last_modified <
(transaction_timestamp() AT TIME ZONE 'UTC' - '1 months' :: INTERVAL))
AND iseg.st_ino_target = main.inode_id
LEFT JOIN
gorfs.nodes AS medium
ON medium.parent_inode_id = main.parent_inode_id
AND medium.relative_path = 'medium'
AND medium.object_type = 'S_IFREG'
LEFT JOIN
gorfs.nodes AS thumbnail
ON thumbnail.parent_inode_id = main.parent_inode_id
AND thumbnail.relative_path = 'thumbnail'
AND thumbnail.object_type = 'S_IFREG'
LIMIT
100;
INDEX CREATED:
CREATE INDEX CONCURRENTLY ix_inode_segments_nfs_file_path on gorfs.inode_segments USING btree ("full_path");
full_path:
ALTER TABLE gorfs.inode_segments ADD COLUMN full_path "gorfs"."absolute_pathname";
EXPLAIN ANALYZE:
"Limit (cost=1935606.57..4178326.49 rows=1 width=170) (actual time=199195.079..315313.338 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=1935606.57..4178326.49 rows=1 width=170) (actual time=199195.076..315313.089 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=1935087.58..4177095.71 rows=1 width=138) (actual time=199195.015..315156.343 rows=100 loops=1)"
" -> Nested Loop (cost=1934568.58..4176379.93 rows=1 width=98) (actual time=199162.474..314565.271 rows=100 loops=1)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" Rows Removed by Join Filter: 34533"
" -> Nested Loop (cost=1934049.58..4175860.39 rows=1 width=103) (actual time=196125.245..314086.043 rows=34633 loops=1)"
" -> Nested Loop (cost=1934049.58..4175847.02 rows=1 width=86) (actual time=196125.213..305961.431 rows=34634 loops=1)"
" -> Hash Join (cost=1934049.58..4175833.65 rows=1 width=94) (actual time=196094.683..238436.508 rows=34634 loops=1)"
" Hash Cond: ((("p"."st_ino")::bigint = ("iseg"."st_ino")::bigint) AND (("p"."st_ino_target")::bigint = ("iseg"."st_ino_target")::bigint))"
" -> Seq Scan on "inode_segments" "p" (cost=0.00..2233425.84 rows=303935 width=78) (actual time=0.046..34047.515 rows=4466887 loops=1)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE ("segment_index")::character varying END)::"text" = 'main'::"text")"
" Rows Removed by Filter: 25643122"
" -> Hash (cost=1929490.56..1929490.56 rows=303935 width=16) (actual time=195921.025..195921.025 rows=40682288 loops=1)"
" Buckets: 32768 Batches: 128 (originally 1) Memory Usage: 16385kB"
" -> Seq Scan on "inode_segments" "iseg" (cost=0.00..1929490.56 rows=303935 width=16) (actual time=0.002..112215.501 rows=60787096 loops=1)"
" Filter: ("nfs_migration_date" IS NULL)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual time=1.942..1.943 rows=1 loops=34634)"
" 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.36 rows=1 width=29) (actual time=0.226..0.228 rows=1 loops=34634)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" Filter: (("st_mtime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '1 mon'::interval))"
" Rows Removed by Filter: 0"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (actual time=0.004..0.009 rows=1 loops=34633)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" Rows Removed by Filter: 22"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual time=0.003..0.035 rows=23 loops=1)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (actual time=0.096..0.796 rows=23 loops=1)"
" SubPlan 5"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.023..0.024 rows=1 loops=23)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (actual time=0.009..0.013 rows=3 loops=23)"
" -> Nested Loop (cost=519.00..715.77 rows=1 width=48) (actual time=5.864..5.904 rows=1 loops=100)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..196.22 rows=1 width=33) (actual time=5.374..5.400 rows=1 loops=100)"
" -> Nested Loop (cost=0.00..182.86 rows=1 width=16) (actual time=4.802..4.809 rows=1 loops=100)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."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_inos" on "inode_segments" "p" (cost=0.00..169.50 rows=1 width=16) (actual time=4.788..4.790 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE ("segment_index")::character varying END)::"text" = 'thumbnail'::"text")"
" Rows Removed by Filter: 1"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.35 rows=1 width=21) (actual time=0.589..0.591 rows=1 loops=96)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (actual time=0.007..0.019 rows=1 loops=96)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" Rows Removed by Filter: 22"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual time=0.005..0.037 rows=23 loops=1)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (actual time=0.100..0.788 rows=23 loops=1)"
" SubPlan 11"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.023..0.024 rows=1 loops=23)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (actual time=0.008..0.012 rows=3 loops=23)"
" SubPlan 3"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (actual time=0.492..0.493 rows=1 loops=96)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (actual time=0.472..0.474 rows=1 loops=96)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..715.77 rows=1 width=48) (actual time=0.034..0.034 rows=0 loops=100)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..196.22 rows=1 width=33) (actual time=0.032..0.032 rows=0 loops=100)"
" -> Nested Loop (cost=0.00..182.86 rows=1 width=16) (actual time=0.029..0.029 rows=0 loops=100)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."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_inos" on "inode_segments" "p" (cost=0.00..169.50 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE ("segment_index")::character varying END)::"text" = 'medium'::"text")"
" Rows Removed by Filter: 2"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.35 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.52 rows=1 width=72) (never executed)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" 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 8"
" -> 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)"
" SubPlan 1"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (actual time=1.523..1.524 rows=1 loops=100)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (actual time=1.512..1.514 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
"Total runtime: 315725.301 ms"
nfs_file_path - COLUMN
ALTER TABLE gorfs.inode_segments ADD COLUMN nfs_file_path "text";
nfs_migration_date - COLUMN - HAVE TO CREATE AN INDEX TO IT
ALTER TABLE gorfs.inode_segments ADD COLUMN nfs_migration_date timestamp without time zone;
TABLE gorfs.inode_segments:
CREATE TABLE gorfs.inode_segments
(
st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms the table's primary key to ensure uniqueness per relevant scope
segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details. The meaning of this column varies based on the host inode type:...
st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for directory inode segments (objects in the directory)
full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical path for quick lookups. Meaningful only for directory inode segments (objects in the directory)
segment_data "bytea", -- Actual data segment. Meaningful only for S_IFLNK and S_IFREG....
nfs_file_path "text",
nfs_migration_date timestamp without time zone,
CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
)
WITH (
OIDS=FALSE
);
Sent from my phone
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow Query - PostgreSQL 9.2
@ 2016-01-12 15:19 bricklen <bricklen@gmail.com>
parent: Saulo Merlo <smerlo50@outlook.com>
0 siblings, 1 reply; 6+ messages in thread
From: bricklen @ 2016-01-12 15:19 UTC (permalink / raw)
To: Saulo Merlo <smerlo50@outlook.com>; +Cc: pgsql-admin
On Tue, Jan 12, 2016 at 12:58 AM, Saulo Merlo <smerlo50@outlook.com> wrote:
> I've been trying to make this Query faster, but I had no success.
>
> Do you guys have an idea about what else I can do?
>
It would be helpful to supply the output of "\d gorfs.nodes" and "\d
gorfs.inode_segments" so we can see the actual indexes and constraints that
exist.
Also, pasting your EXPLAIN plan into http://explain.depesz.com/ and
submitting the link in your post is usually easier to read than pasting it
into an email.
Try the following partial indexes (they may or may not already exist, the
structure of the tables is incomplete)
create index concurrently inode_segments_st_ino_st_ino_target_pidx
on gorfs.inode_segments (st_ino desc, st_ino_target desc)
where nfs_migration_data is null;
-- if there is no index on gorfs.nodes.last_modified, test a partial index:
create index concurrently nodes_last_modified_rel_path_obj_type_pidx
on gorfs.nodes (last_modified desc)
where relative_path = 'main'
and object_type = 'S_IFREG';
vacuum analyze verbose gorfs.nodes;
vacuum analyze verbose gorfs.inode_segments;
EXPLAIN (analyze, buffers)
SELECT main.inode_id AS file_id,
main.file_data AS main_binary,
main.node_full_path AS filename,
main.last_modified AS date_created,
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 main
INNER JOIN gorfs.inode_segments AS iseg ON
(iseg.st_ino = main.parent_inode_id
AND iseg.st_ino_target = main.inode_id)
LEFT JOIN gorfs.nodes AS medium ON
(medium.parent_inode_id = main.parent_inode_id
AND medium.relative_path = 'medium'
AND medium.object_type = 'S_IFREG')
LEFT JOIN gorfs.nodes AS thumbnail ON
(thumbnail.parent_inode_id = main.parent_inode_id
AND thumbnail.relative_path = 'thumbnail'
AND thumbnail.object_type = 'S_IFREG')
WHERE main.relative_path = 'main'
AND main.object_type = 'S_IFREG'
AND iseg.nfs_migration_date IS NULL
AND (main.last_modified < (transaction_timestamp() AT TIME ZONE 'UTC' - '1
months' :: INTERVAL))
LIMIT 100;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow Query - PostgreSQL 9.2
@ 2016-01-12 20:28 Saulo Merlo <smerlo50@outlook.com>
parent: bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Saulo Merlo @ 2016-01-12 20:28 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-admin
Hi Bricklen.. Thank you for your help... Thank you so much...
It would be helpful to supply the output of "\d gorfs.nodes" and "\d gorfs.inode_segments" so we can see the actual indexes and constraints that exist.
Remember that gorfs.nodes is a view.
I've created the INDEX you told me to:create index concurrently inode_segments_st_ino_target_pidx on gorfs.inode_segments (st_ino desc, st_ino_target desc) where nfs_migration_date is null;
Link: http://explain.depesz.com/s/Swu
It seems on the EXPLAIN ANALYZE that it wasn't used!
gorfs.nodes:
DBNAME=# \d gorfs.nodes teste5.txt View "gorfs.nodes" Column | Type | Modifiers--------------------+--------------------------------+----------- node_full_path | "gorfs"."absolute_pathname" | parent_inode_id | "gorfs"."ino_t" | inode_id | "gorfs"."ino_t" | relative_path | character varying | raw_mode | bigint | object_type | "gorfs"."mode_t_constant_name" | setuid | boolean | setgid | boolean | sticky | boolean | permissions | bit(9) | links_count | "gorfs"."nlink_t" | owner_uid | "gorfs"."uid_t" | owner_gid | "gorfs"."gid_t" | data_length | "gorfs"."off_t" | last_accessed | "gorfs"."time_t" | last_modified | "gorfs"."time_t" | last_changed | "gorfs"."time_t" | checksum_md5 | "md5_hash" | media_type | "text" | target | "text" | file_data | "bytea" | is_external | boolean | data_length_target | "gorfs"."off_t" |Triggers: "i_iud_action_changes" INSTEAD OF INSERT OR DELETE OR UPDATE ON "gorfs"."nodes" FOR EACH ROW EXECUTE PROCEDURE"gorfs"."tf_nodes_action_changes"()
gorfs.inode_segments:
Table "gorfs.inode_segments" Column | Type | Modifiers--------------------+------------------------------+----------- st_ino | "gorfs"."ino_t" | not null segment_index | "gorfs"."pathname_component" | not null st_ino_target | "gorfs"."ino_t" | full_path | "gorfs"."absolute_pathname" | segment_data | "bytea" | nfs_migration_date | timestamp with time zone | nfs_file_path | "text" |Indexes: "pk_inode_segments" PRIMARY KEY, "btree" ("st_ino", "segment_index") "uc_no_duplicate_full_paths" UNIQUE CONSTRAINT, "btree" ("full_path") "inode_segments_st_ino_target_pidx" "btree" ("st_ino" DESC, "st_ino_target" DESC) WHERE "nfs_migration_date" IS NULL "ix_inode_segments_climb_tree" "btree" ("segment_index", "st_ino_target") "ix_inode_segments_filter_by_subtree" "btree" ("full_path" "varchar_pattern_ops") WHERE "full_path" IS NOT NULL "ix_inode_segments_full_path_resolution" "btree" ("st_ino", "full_path") "ix_inode_segments_gsdi_pk" "btree" (("st_ino"::"text"), ("segment_index"::"text")) "ix_inode_segments_ja_files_lookup" "btree" ((CASE WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"text", '.*\.'::"text", ''::"text", 'g'::"text")) ELSE NULL::"text"END)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode_segments_nfs_file_path" "btree" ("full_path") "ix_inode_segments_nfs_migration_date" "btree" ("nfs_migration_date") WHERE "nfs_migration_date" IS NULL "ix_inode_segments_nfs_st_ino" "btree" ("st_ino") "ix_inode_segments_notes_clientids" "btree" (("split_part"("full_path"::"text", '/'::"text", 4)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode_segments_notes_fileids" "btree" (("split_part"("full_path"::"text", '/'::"text", 8)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode_segments_notes_noteids" "btree" ((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 'unassigned'::"text")::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode_segments_segment_indexes" "btree" ("segment_index") "ix_inode_segments_st_ino_targets" "btree" ("st_ino_target") "ix_inode_segments_st_inos" "btree" ("st_ino")Check constraints: "cc_only_root_can_be_its_own_parent" CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR "st_ino"::bigint = 2)Foreign-key constraints: "fk_host_inode_must_exist" FOREIGN KEY ("st_ino") REFERENCES "gorfs"."inodes"("st_ino") "fk_target_inode_must_exist" FOREIGN KEY ("st_ino_target") REFERENCES "gorfs"."inodes"("st_ino")Triggers: "a_iud_update_inode" AFTER INSERT OR DELETE OR UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_update_inodes"() "a_u_update_children" AFTER UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_update_children"() "b_iu_calculate_columns" BEFORE INSERT OR UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_calculate_columns"() "ct_valid_data_layouts_only" AFTER INSERT ON "gorfs"."inode_segments" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_valid_data_layouts_only"()
Also, pasting your EXPLAIN plan into http://explain.depesz.com/ and submitting the link in your post is usually easier to read than pasting it into an email.
Ok. I'll do it.
create index concurrently nodes_last_modified_rel_path_obj_type_pidx
on gorfs.nodes (last_modified desc)
where relative_path = 'main'
and object_type = 'S_IFREG';
gorfs.nodes is a view:
CREATE OR REPLACE VIEW gorfs.nodes AS SELECT "p"."full_path" AS "node_full_path", "h"."st_ino" AS "parent_inode_id", "t"."st_ino" AS "inode_id", CASE WHEN "p"."st_ino_target"::bigint = 2 THEN NULL::character varying ELSE "p"."segment_index"::character varying END AS "relative_path", "t"."st_mode"::bigint AS "raw_mode", "f"."constant_name" AS "object_type", ("gorfs"."mode_t_bits"('S_ISUID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "setuid", ("gorfs"."mode_t_bits"('S_ISGID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "setgid", ("gorfs"."mode_t_bits"('S_ISVTX'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "sticky", "right"("concat"((("gorfs"."mode_t_bits"('S_IRWXU'::"gorfs"."mode_t_constant_name")::"bit" | "gorfs"."mode_t_bits"('S_IRWXG'::"gorfs"."mode_t_constant_name")::"bit") | "gorfs"."mode_t_bits"('S_IRWXO'::"gorfs"."mode_t_constant_name")::"bit") & "t"."st_mode"::"bit"), 9)::bit(9) AS "permissions", "t"."st_nlink" AS "links_count", "t"."st_uid" AS "owner_uid", "t"."st_gid" AS "owner_gid", "t"."st_size" AS "data_length", "t"."st_atime" AS "last_accessed", "t"."st_mtime" AS "last_modified", "t"."st_ctime" AS "last_changed", "t"."checksum_md5", ("mst"."media_type" || '/'::"text") || "mst"."subtype_string"::"text" AS "media_type", CASE WHEN "f"."constant_name" = 'S_IFLNK'::"gorfs"."mode_t_constant_name" THEN ( SELECT "convert_from"("ls"."segment_data", 'UTF8'::"name") AS "convert_from" FROM "gorfs"."inode_segments" "ls" WHERE "ls"."st_ino"::bigint = "p"."st_ino_target"::bigint) ELSE NULL::"text" END AS "target", CASE WHEN "f"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name" THEN ( SELECT "string_agg"("fs"."segment_data", ''::"bytea" ORDER BY "fs"."segment_index") AS "string_agg" FROM "gorfs"."inode_segments" "fs" WHERE "fs"."st_ino"::bigint = "p"."st_ino_target"::bigint) ELSE NULL::"bytea" END AS "file_data", "t"."external_size" IS NOT NULL AS "is_external", "t"."external_size" AS "data_length_target" FROM "gorfs"."inode_segments" "p" JOIN "gorfs"."dir_inodes" "h" ON "h"."st_ino"::bigint = "p"."st_ino"::bigint JOIN "gorfs"."inodes" "t" ON "t"."st_ino"::bigint = "p"."st_ino_target"::bigint JOIN "gorfs"."mode_t_flags"() "f"("constant_name", "description", "bits", "bits_octal", "bits_hex", "bits_decimal") ON "f"."bits"::"bit" = ("gorfs"."mode_t_bits"('S_IFMT'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit") LEFT JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" = "t"."media_subtype_id"; =
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow Query - PostgreSQL 9.2
@ 2016-01-12 21:05 Saulo Merlo <smerlo50@outlook.com>
parent: Saulo Merlo <smerlo50@outlook.com>
0 siblings, 0 replies; 6+ messages in thread
From: Saulo Merlo @ 2016-01-12 21:05 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-admin
I also am able to create a temporary table to store migrations, which may be the best option (no longer need to join new columns in query)
If you could help with that as well..Thank you
From: smerlo50@outlook.com
To: bricklen@gmail.com
CC: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Slow Query - PostgreSQL 9.2
Date: Tue, 12 Jan 2016 20:28:53 +0000
Hi Bricklen.. Thank you for your help... Thank you so much...
It would be helpful to supply the output of "\d gorfs.nodes" and "\d gorfs.inode_segments" so we can see the actual indexes and constraints that exist.
Remember that gorfs.nodes is a view.
I've created the INDEX you told me to:create index concurrently inode_segments_st_ino_target_pidx on gorfs.inode_segments (st_ino desc, st_ino_target desc) where nfs_migration_date is null;
Link: http://explain.depesz.com/s/Swu
It seems on the EXPLAIN ANALYZE that it wasn't used!
gorfs.nodes:
DBNAME=# \d gorfs.nodes teste5.txt View "gorfs.nodes" Column | Type | Modifiers--------------------+--------------------------------+----------- node_full_path | "gorfs"."absolute_pathname" | parent_inode_id | "gorfs"."ino_t" | inode_id | "gorfs"."ino_t" | relative_path | character varying | raw_mode | bigint | object_type | "gorfs"."mode_t_constant_name" | setuid | boolean | setgid | boolean | sticky | boolean | permissions | bit(9) | links_count | "gorfs"."nlink_t" | owner_uid | "gorfs"."uid_t" | owner_gid | "gorfs"."gid_t" | data_length | "gorfs"."off_t" | last_accessed | "gorfs"."time_t" | last_modified | "gorfs"."time_t" | last_changed | "gorfs"."time_t" | checksum_md5 | "md5_hash" | media_type | "text" | target | "text" | file_data | "bytea" | is_external | boolean | data_length_target | "gorfs"."off_t" |Triggers: "i_iud_action_changes" INSTEAD OF INSERT OR DELETE OR UPDATE ON "gorfs"."nodes" FOR EACH ROW EXECUTE PROCEDURE"gorfs"."tf_nodes_action_changes"()
gorfs.inode_segments:
Table "gorfs.inode_segments" Column | Type | Modifiers--------------------+------------------------------+----------- st_ino | "gorfs"."ino_t" | not null segment_index | "gorfs"."pathname_component" | not null st_ino_target | "gorfs"."ino_t" | full_path | "gorfs"."absolute_pathname" | segment_data | "bytea" | nfs_migration_date | timestamp with time zone | nfs_file_path | "text" |Indexes: "pk_inode_segments" PRIMARY KEY, "btree" ("st_ino", "segment_index") "uc_no_duplicate_full_paths" UNIQUE CONSTRAINT, "btree" ("full_path") "inode_segments_st_ino_target_pidx" "btree" ("st_ino" DESC, "st_ino_target" DESC) WHERE "nfs_migration_date" IS NULL "ix_inode_segments_climb_tree" "btree" ("segment_index", "st_ino_target") "ix_inode_segments_filter_by_subtree" "btree" ("full_path" "varchar_pattern_ops") WHERE "full_path" IS NOT NULL "ix_inode_segments_full_path_resolution" "btree" ("st_ino", "full_path") "ix_inode_segments_gsdi_pk" "btree" (("st_ino"::"text"), ("segment_index"::"text")) "ix_inode_segments_ja_files_lookup" "btree" ((CASE WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"text", '.*\.'::"text", ''::"text", 'g'::"text")) ELSE NULL::"text"END)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode_segments_nfs_file_path" "btree" ("full_path") "ix_inode_segments_nfs_migration_date" "btree" ("nfs_migration_date") WHERE "nfs_migration_date" IS NULL "ix_inode_segments_nfs_st_ino" "btree" ("st_ino") "ix_inode_segments_notes_clientids" "btree" (("split_part"("full_path"::"text", '/'::"text", 4)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode_segments_notes_fileids" "btree" (("split_part"("full_path"::"text", '/'::"text", 8)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode_segments_notes_noteids" "btree" ((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 'unassigned'::"text")::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode_segments_segment_indexes" "btree" ("segment_index") "ix_inode_segments_st_ino_targets" "btree" ("st_ino_target") "ix_inode_segments_st_inos" "btree" ("st_ino")Check constraints: "cc_only_root_can_be_its_own_parent" CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR "st_ino"::bigint = 2)Foreign-key constraints: "fk_host_inode_must_exist" FOREIGN KEY ("st_ino") REFERENCES "gorfs"."inodes"("st_ino") "fk_target_inode_must_exist" FOREIGN KEY ("st_ino_target") REFERENCES "gorfs"."inodes"("st_ino")Triggers: "a_iud_update_inode" AFTER INSERT OR DELETE OR UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_update_inodes"() "a_u_update_children" AFTER UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_update_children"() "b_iu_calculate_columns" BEFORE INSERT OR UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_calculate_columns"() "ct_valid_data_layouts_only" AFTER INSERT ON "gorfs"."inode_segments" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_valid_data_layouts_only"()
Also, pasting your EXPLAIN plan into http://explain.depesz.com/ and submitting the link in your post is usually easier to read than pasting it into an email.
Ok. I'll do it.
create index concurrently nodes_last_modified_rel_path_obj_type_pidx
on gorfs.nodes (last_modified desc)
where relative_path = 'main'
and object_type = 'S_IFREG';
gorfs.nodes is a view:
CREATE OR REPLACE VIEW gorfs.nodes AS SELECT "p"."full_path" AS "node_full_path", "h"."st_ino" AS "parent_inode_id", "t"."st_ino" AS "inode_id", CASE WHEN "p"."st_ino_target"::bigint = 2 THEN NULL::character varying ELSE "p"."segment_index"::character varying END AS "relative_path", "t"."st_mode"::bigint AS "raw_mode", "f"."constant_name" AS "object_type", ("gorfs"."mode_t_bits"('S_ISUID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "setuid", ("gorfs"."mode_t_bits"('S_ISGID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "setgid", ("gorfs"."mode_t_bits"('S_ISVTX'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "sticky", "right"("concat"((("gorfs"."mode_t_bits"('S_IRWXU'::"gorfs"."mode_t_constant_name")::"bit" | "gorfs"."mode_t_bits"('S_IRWXG'::"gorfs"."mode_t_constant_name")::"bit") | "gorfs"."mode_t_bits"('S_IRWXO'::"gorfs"."mode_t_constant_name")::"bit") & "t"."st_mode"::"bit"), 9)::bit(9) AS "permissions", "t"."st_nlink" AS "links_count", "t"."st_uid" AS "owner_uid", "t"."st_gid" AS "owner_gid", "t"."st_size" AS "data_length", "t"."st_atime" AS "last_accessed", "t"."st_mtime" AS "last_modified", "t"."st_ctime" AS "last_changed", "t"."checksum_md5", ("mst"."media_type" || '/'::"text") || "mst"."subtype_string"::"text" AS "media_type", CASE WHEN "f"."constant_name" = 'S_IFLNK'::"gorfs"."mode_t_constant_name" THEN ( SELECT "convert_from"("ls"."segment_data", 'UTF8'::"name") AS "convert_from" FROM "gorfs"."inode_segments" "ls" WHERE "ls"."st_ino"::bigint = "p"."st_ino_target"::bigint) ELSE NULL::"text" END AS "target", CASE WHEN "f"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name" THEN ( SELECT "string_agg"("fs"."segment_data", ''::"bytea" ORDER BY "fs"."segment_index") AS "string_agg" FROM "gorfs"."inode_segments" "fs" WHERE "fs"."st_ino"::bigint = "p"."st_ino_target"::bigint) ELSE NULL::"bytea" END AS "file_data", "t"."external_size" IS NOT NULL AS "is_external", "t"."external_size" AS "data_length_target" FROM "gorfs"."inode_segments" "p" JOIN "gorfs"."dir_inodes" "h" ON "h"."st_ino"::bigint = "p"."st_ino"::bigint JOIN "gorfs"."inodes" "t" ON "t"."st_ino"::bigint = "p"."st_ino_target"::bigint JOIN "gorfs"."mode_t_flags"() "f"("constant_name", "description", "bits", "bits_octal", "bits_hex", "bits_decimal") ON "f"."bits"::"bit" = ("gorfs"."mode_t_bits"('S_IFMT'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit") LEFT JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" = "t"."media_subtype_id"; =
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2016-01-12 21:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-01-11 04:30 Slow Query - PostgreSQL 9.2 Saulo Merlo <smerlo50@outlook.com>
2016-01-11 06:08 ` Franz Timmer <ml@ft-c.de>
2016-01-12 08:58 Slow Query - PostgreSQL 9.2 Saulo Merlo <smerlo50@outlook.com>
2016-01-12 15:19 ` bricklen <bricklen@gmail.com>
2016-01-12 20:28 ` Saulo Merlo <smerlo50@outlook.com>
2016-01-12 21:05 ` Saulo Merlo <smerlo50@outlook.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox