Result: 9lg

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
0.008	65196.993	↑ 1.0	2	1	→ Result (cost=16.74..1252843.09 rows=2 width=132) (actual time=0.134..65196.993 rows=2 loops=1)
					CTE parent
0.007	0.095	↓ 2.0	2	1	→ Limit (cost=16.70..16.71 rows=1 width=175) (actual time=0.089..0.095 rows=2 loops=1)
0.027	0.088	↓ 2.0	2	1	→ Sort (cost=16.70..16.71 rows=1 width=175) (actual time=0.086..0.088 rows=2 loops=1) Sort Key: guestbook.posted Sort Method: quicksort Memory: 25kB
0.061	0.061	↓ 2.0	2	1	→ Index Scan using guestbook_user_name_photo_identifier_idx on guestbook (cost=0.00..16.69 rows=1 width=175) (actual time=0.056..0.061 rows=2 loops=1) Index Cond: ((user_name = 'duchemol'::text) AND (photo_identifier = 119000000000000001::bigint)) Filter: (parent_identifier IS NULL)
0.006	65196.985	↑ 1.0	2	1	→ Append (cost=0.03..1252826.38 rows=2 width=132) (actual time=0.130..65196.985 rows=2 loops=1)
0.027	0.130	↓ 2.0	2	1	→ Sort (cost=0.03..0.04 rows=1 width=89) (actual time=0.128..0.130 rows=2 loops=1) Sort Key: p.posted Sort Method: quicksort Memory: 25kB
0.103	0.103	↓ 2.0	2	1	→ CTE Scan on parent p (cost=0.00..0.02 rows=1 width=89) (actual time=0.093..0.103 rows=2 loops=1)
0.030	65196.849	↓ 0.0	0	1	→ Sort (cost=1252826.32..1252826.33 rows=1 width=175) (actual time=65196.849..65196.849 rows=0 loops=1) Sort Key: gn.parent_identifier, gn.posted Sort Method: quicksort Memory: 25kB
29378.937	65196.819	↓ 0.0	0	1	→ Hash Semi Join (cost=0.03..1252826.31 rows=1 width=175) (actual time=65196.819..65196.819 rows=0 loops=1) Hash Cond: (gn.parent_identifier = parent.identifier)
35817.872	35817.872	↓ 1.0	30293553	1	→ Seq Scan on guestbook gn (cost=0.00..1174315.33 rows=29908933 width=175) (actual time=0.006..35817.872 rows=30293553 loops=1)
0.006	0.010	↓ 2.0	2	1	→ Hash (cost=0.02..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
0.004	0.004	↓ 2.0	2	1	→ CTE Scan on parent (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.004 rows=2 loops=1)

                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=16.74..1252843.09 rows=2 width=132) (actual time=0.134..65196.993 rows=2 loops=1)
   CTE parent
     ->  Limit  (cost=16.70..16.71 rows=1 width=175) (actual time=0.089..0.095 rows=2 loops=1)
           ->  Sort  (cost=16.70..16.71 rows=1 width=175) (actual time=0.086..0.088 rows=2 loops=1)
                 Sort Key: guestbook.posted
                 Sort Method: quicksort  Memory: 25kB
                 ->  Index Scan using guestbook_user_name_photo_identifier_idx on guestbook  (cost=0.00..16.69 rows=1 width=175) (actual time=0.056..0.061 rows=2 loops=1)
                       Index Cond: ((user_name = 'duchemol'::text) AND (photo_identifier = 119000000000000001::bigint))
                       Filter: (parent_identifier IS NULL)
   ->  Append  (cost=0.03..1252826.38 rows=2 width=132) (actual time=0.130..65196.985 rows=2 loops=1)
         ->  Sort  (cost=0.03..0.04 rows=1 width=89) (actual time=0.128..0.130 rows=2 loops=1)
               Sort Key: p.posted
               Sort Method: quicksort  Memory: 25kB
               ->  CTE Scan on parent p  (cost=0.00..0.02 rows=1 width=89) (actual time=0.093..0.103 rows=2 loops=1)
         ->  Sort  (cost=1252826.32..1252826.33 rows=1 width=175) (actual time=65196.849..65196.849 rows=0 loops=1)
               Sort Key: gn.parent_identifier, gn.posted
               Sort Method: quicksort  Memory: 25kB
               ->  Hash Semi Join  (cost=0.03..1252826.31 rows=1 width=175) (actual time=65196.819..65196.819 rows=0 loops=1)
                     Hash Cond: (gn.parent_identifier = parent.identifier)
                     ->  Seq Scan on guestbook gn  (cost=0.00..1174315.33 rows=29908933 width=175) (actual time=0.006..35817.872 rows=30293553 loops=1)
                     ->  Hash  (cost=0.02..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=2 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 1kB
                           ->  CTE Scan on parent  (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.004 rows=2 loops=1)
 Total runtime: 65197.185 ms
(24 rows)

Per node type stats

node type	count	sum of times	% of query
Append	1	0.006 ms	0.0 %
CTE Scan	2	0.107 ms	0.0 %
Hash	1	0.006 ms	0.0 %
Hash Semi Join	1	29378.937 ms	45.1 %
Index Scan	1	0.061 ms	0.0 %
Limit	1	0.007 ms	0.0 %
Result	1	0.008 ms	0.0 %
Seq Scan	1	35817.872 ms	54.9 %
Sort	3	0.084 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
guestbook	2	35817.933 ms	54.9 %
Index Scan	1	0.061 ms	0.0 %
Seq Scan	1	35817.872 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 9lg

Colorize:

Columns visibility:

Per node type stats

Per table stats