Home > mailing lists

Slow Query - PostgreSQL 9.2 - Mailing list pgsql-admin

From	Saulo Merlo
Subject	Slow Query - PostgreSQL 9.2
Date	January 11, 2016 07:30:34
Msg-id	SNT147-W3773C4758CACA88094B6D8D3C90@phx.gbl Whole thread Raw
Responses	Re: Slow Query - PostgreSQL 9.2 (Franz Timmer <ml@ft-c.de>)
List	pgsql-admin

Tree view

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.

pgsql-admin by date:

From: Saulo Merlo
Date: 10 January 2016, 22:52:17
Subject: Large Binary Columns - Slow Query

From: Franz Timmer
Date: 11 January 2016, 09:09:12
Subject: Re: Slow Query - PostgreSQL 9.2

Slow Query - PostgreSQL 9.2 - Mailing list pgsql-admin

Previous

Next