#	exclusive	inclusive	rows x	rows	loops	node
1.	15.223	54023.968	↓ 1.0	553	1	→ Seq Scan on ticket t (cost=0.00..5934998.36 rows=551 width=73) (actual time=85.326..54023.968 rows=553 loops=1) Filter: (create_time > '2012-04-01 00:00:00'::timestamp without time zone)
2.						SubPlan (forSeq Scan)
3.	56.406	53655.931	↑ 1.0	1	553	→ Aggregate (cost=10408.25..10408.26 rows=1 width=16) (actual time=97.026..97.027 rows=1 loops=553)
4.	111.053	53599.525	↓ 67.0	67	553	→ Hash Semi Join (cost=10408.21..10408.25 rows=1 width=16) (actual time=96.640..96.925 rows=67 loops=553) Hash Cond: (h2.state_id = ticket_state.id)
5.	124.425	53488.372	↓ 38.5	77	553	→ Sort (cost=10405.65..10405.66 rows=2 width=22) (actual time=96.632..96.724 rows=77 loops=553) Sort Key: h1.id Sort Method: quicksort Memory: 31kB
6.	117.236	53363.947	↓ 38.5	77	553	→ Unique (cost=10405.61..10405.64 rows=2 width=22) (actual time=96.199..96.499 rows=77 loops=553)
7.	176.960	53246.711	↓ 38.5	77	553	→ Sort (cost=10405.61..10405.62 rows=2 width=22) (actual time=96.195..96.287 rows=77 loops=553) Sort Key: h1.ticket_id, h1.id, h2.id, ((h1.create_time - h2.create_time)), h2.state_id Sort Method: quicksort Memory: 31kB
8.	4764.648	53069.751	↓ 38.5	77	553	→ Append (cost=325.51..10405.60 rows=2 width=22) (actual time=28.005..95.967 rows=77 loops=553)
9.	415.995	48294.596	↓ 76.0	76	553	→ Hash Join (cost=325.51..10304.48 rows=1 width=32) (actual time=28.000..87.332 rows=76 loops=553) Hash Cond: ((SubPlan 5) = h2.id)
10.	87.927	87.927	↑ 1.4	77	553	→ Index Scan using ticket_history_ticket_id on ticket_history h1 (cost=0.00..324.19 rows=105 width=16) (actual time=0.008..0.159 rows=77 loops=553) Index Cond: (ticket_id = $1)
11.	68.019	214.564	↑ 1.4	77	553	→ Hash (cost=324.19..324.19 rows=105 width=20) (actual time=0.388..0.388 rows=77 loops=553)
12.	146.545	146.545	↑ 1.4	77	553	→ Index Scan using ticket_history_ticket_id on ticket_history h2 (cost=0.00..324.19 rows=105 width=20) (actual time=0.110..0.265 rows=77 loops=553) Index Cond: (ticket_id = $1)
13.						SubPlan (forHash Join)
14.	507.930	47576.110	↑ 1.0	1	84655	→ Result (cost=183.88..183.89 rows=1 width=0) (actual time=0.560..0.562 rows=1 loops=84655)
15.						Initplan (forResult)
16.	338.620	47068.180	↑ 1.0	1	84655	→ Limit (cost=0.00..183.88 rows=1 width=4) (actual time=0.555..0.556 rows=1 loops=84655)
17.	46729.560	46729.560	↑ 35.0	1	84655	→ Index Scan Backward using ticket_history_pkey on ticket_history h3 (cost=0.00..6435.90 rows=35 width=4) (actual time=0.552..0.552 rows=1 loops=84655) Index Cond: (id < $4) Filter: ((id IS NOT NULL) AND ($5 = ticket_id))
18.	0.000	10.507	↑ 1.0	1	553	→ Index Scan using ticket_history_pkey on ticket_history h4 (cost=92.81..101.10 rows=1 width=12) (actual time=0.017..0.019 rows=1 loops=553) Index Cond: (id = $3)
19.						Initplan (forIndex Scan)
20.	4.977	4647.412	↑ 1.0	1	553	→ Result (cost=92.80..92.81 rows=1 width=0) (actual time=8.402..8.404 rows=1 loops=553)
21.						Initplan (forResult)
22.	3.318	4642.435	↑ 1.0	1	553	→ Limit (cost=0.00..92.80 rows=1 width=4) (actual time=8.393..8.395 rows=1 loops=553)
23.	4639.117	4639.117	↑ 105.0	1	553	→ Index Scan Backward using ticket_history_pkey on ticket_history (cost=0.00..9744.34 rows=105 width=4) (actual time=8.389..8.389 rows=1 loops=553) Filter: ((id IS NOT NULL) AND (ticket_id = $1))
24.	0.012	0.100	↓ 5.0	5	1	→ Hash (cost=2.54..2.54 rows=1 width=4) (actual time=0.100..0.100 rows=5 loops=1)
25.	0.072	0.088	↓ 5.0	5	1	→ Seq Scan on ticket_state (cost=1.09..2.54 rows=1 width=4) (actual time=0.066..0.088 rows=5 loops=1) Filter: ((NOT (hashed SubPlan 1)) AND (valid_id = 1) AND ((name)::text <> ALL ('{hold,"awaiting client",resolved,"pending close",scheduled}'::text[])))
26.						SubPlan (forSeq Scan)
27.	0.016	0.016	↑ 1.0	2	1	→ Seq Scan on ticket_state_type (cost=0.00..1.09 rows=2 width=4) (actual time=0.011..0.016 rows=2 loops=1) Filter: ((name)::text = ANY ('{closed,removed}'::text[]))
28.	4.424	137.697	↑ 1.0	1	553	→ Limit (cost=35.65..35.65 rows=1 width=8) (actual time=0.248..0.249 rows=1 loops=553)
29.	11.060	133.273	↑ 1.0	1	553	→ Sort (cost=35.65..35.65 rows=1 width=8) (actual time=0.241..0.241 rows=1 loops=553) Sort Key: a.create_time Sort Method: quicksort Memory: 25kB
30.	33.342	122.213	↑ 1.0	1	553	→ Nested Loop (cost=1.25..35.64 rows=1 width=8) (actual time=0.105..0.221 rows=1 loops=553) Join Filter: (a.article_type_id = art.id)
31.	15.484	65.807	↓ 1.2	5	553	→ Nested Loop (cost=0.00..34.12 rows=4 width=12) (actual time=0.050..0.119 rows=5 loops=553) Join Filter: (a.article_sender_type_id = ast.id)
32.	5.530	5.530	↑ 1.0	1	553	→ Seq Scan on article_sender_type ast (cost=0.00..1.04 rows=1 width=4) (actual time=0.007..0.010 rows=1 loops=553) Filter: ((name)::text = 'agent'::text)
33.	44.793	44.793	↑ 1.4	8	553	→ Index Scan using article_ticket_id on article a (cost=0.00..32.94 rows=11 width=16) (actual time=0.026..0.081 rows=8 loops=553) Index Cond: (a.ticket_id = $1)
34.	23.025	23.064	↓ 1.7	5	2883	→ Materialize (cost=1.25..1.28 rows=3 width=4) (actual time=0.001..0.008 rows=5 loops=2883)
35.	0.039	0.039	↓ 1.7	5	1	→ Seq Scan on article_type art (cost=0.00..1.25 rows=3 width=4) (actual time=0.021..0.039 rows=5 loops=1) Filter: (((name)::text ~~ 'email-ext%'::text) OR ((name)::text ~~ 'note-ext%'::text) OR ((name)::text = 'phone'::text) OR ((name)::text = 'fax'::text) OR ((name)::text = 'sms'::text))
36.	33.180	215.117	↑ 1.0	1	553	→ Aggregate (cost=327.28..327.29 rows=1 width=8) (actual time=0.388..0.389 rows=1 loops=553)
37.	100.015	181.937	↑ 2.2	43	553	→ Hash Join (cost=1.50..327.04 rows=95 width=8) (actual time=0.081..0.329 rows=43 loops=553) Hash Cond: (h.state_id = s.id)
38.	81.844	81.844	↑ 1.4	77	553	→ Index Scan using ticket_history_ticket_id on ticket_history h (cost=0.00..324.19 rows=105 width=12) (actual time=0.013..0.148 rows=77 loops=553) Index Cond: (ticket_id = $1)
39.	0.034	0.078	↑ 1.0	19	1	→ Hash (cost=1.26..1.26 rows=19 width=4) (actual time=0.078..0.078 rows=19 loops=1)
40.	0.044	0.044	↑ 1.0	19	1	→ Seq Scan on ticket_state s (cost=0.00..1.26 rows=19 width=4) (actual time=0.011..0.044 rows=19 loops=1) Filter: ((name)::text <> ALL ('{new,"open no response"}'::text[]))

                                                                                                       QUERY PLAN                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on ticket t  (cost=0.00..5934998.36 rows=551 width=73) (actual time=85.326..54023.968 rows=553 loops=1)
   Filter: (create_time > '2012-04-01 00:00:00'::timestamp without time zone)
   SubPlan 6
     ->  Aggregate  (cost=10408.25..10408.26 rows=1 width=16) (actual time=97.026..97.027 rows=1 loops=553)
           ->  Hash Semi Join  (cost=10408.21..10408.25 rows=1 width=16) (actual time=96.640..96.925 rows=67 loops=553)
                 Hash Cond: (h2.state_id = ticket_state.id)
                 ->  Sort  (cost=10405.65..10405.66 rows=2 width=22) (actual time=96.632..96.724 rows=77 loops=553)
                       Sort Key: h1.id
                       Sort Method:  quicksort  Memory: 31kB
                       ->  Unique  (cost=10405.61..10405.64 rows=2 width=22) (actual time=96.199..96.499 rows=77 loops=553)
                             ->  Sort  (cost=10405.61..10405.62 rows=2 width=22) (actual time=96.195..96.287 rows=77 loops=553)
                                   Sort Key: h1.ticket_id, h1.id, h2.id, ((h1.create_time - h2.create_time)), h2.state_id
                                   Sort Method:  quicksort  Memory: 31kB
                                   ->  Append  (cost=325.51..10405.60 rows=2 width=22) (actual time=28.005..95.967 rows=77 loops=553)
                                         ->  Hash Join  (cost=325.51..10304.48 rows=1 width=32) (actual time=28.000..87.332 rows=76 loops=553)
                                               Hash Cond: ((SubPlan 5) = h2.id)
                                               ->  Index Scan using ticket_history_ticket_id on ticket_history h1  (cost=0.00..324.19 rows=105 width=16) (actual time=0.008..0.159 rows=77 loops=553)
                                                     Index Cond: (ticket_id = $1)
                                               ->  Hash  (cost=324.19..324.19 rows=105 width=20) (actual time=0.388..0.388 rows=77 loops=553)
                                                     ->  Index Scan using ticket_history_ticket_id on ticket_history h2  (cost=0.00..324.19 rows=105 width=20) (actual time=0.110..0.265 rows=77 loops=553)
                                                           Index Cond: (ticket_id = $1)
                                               SubPlan 5
                                                 ->  Result  (cost=183.88..183.89 rows=1 width=0) (actual time=0.560..0.562 rows=1 loops=84655)
                                                       InitPlan 4 (returns $6)
                                                         ->  Limit  (cost=0.00..183.88 rows=1 width=4) (actual time=0.555..0.556 rows=1 loops=84655)
                                                               ->  Index Scan Backward using ticket_history_pkey on ticket_history h3  (cost=0.00..6435.90 rows=35 width=4) (actual time=0.552..0.552 rows=1 loops=84655)
                                                                     Index Cond: (id < $4)
                                                                     Filter: ((id IS NOT NULL) AND ($5 = ticket_id))
                                         ->  Index Scan using ticket_history_pkey on ticket_history h4  (cost=92.81..101.10 rows=1 width=12) (actual time=0.017..0.019 rows=1 loops=553)
                                               Index Cond: (id = $3)
                                               InitPlan 3 (returns $3)
                                                 ->  Result  (cost=92.80..92.81 rows=1 width=0) (actual time=8.402..8.404 rows=1 loops=553)
                                                       InitPlan 2 (returns $2)
                                                         ->  Limit  (cost=0.00..92.80 rows=1 width=4) (actual time=8.393..8.395 rows=1 loops=553)
                                                               ->  Index Scan Backward using ticket_history_pkey on ticket_history  (cost=0.00..9744.34 rows=105 width=4) (actual time=8.389..8.389 rows=1 loops=553)
                                                                     Filter: ((id IS NOT NULL) AND (ticket_id = $1))
                 ->  Hash  (cost=2.54..2.54 rows=1 width=4) (actual time=0.100..0.100 rows=5 loops=1)
                       ->  Seq Scan on ticket_state  (cost=1.09..2.54 rows=1 width=4) (actual time=0.066..0.088 rows=5 loops=1)
                             Filter: ((NOT (hashed SubPlan 1)) AND (valid_id = 1) AND ((name)::text <> ALL ('{hold,"awaiting client",resolved,"pending close",scheduled}'::text[])))
                             SubPlan 1
                               ->  Seq Scan on ticket_state_type  (cost=0.00..1.09 rows=2 width=4) (actual time=0.011..0.016 rows=2 loops=1)
                                     Filter: ((name)::text = ANY ('{closed,removed}'::text[]))
   SubPlan 7
     ->  Limit  (cost=35.65..35.65 rows=1 width=8) (actual time=0.248..0.249 rows=1 loops=553)
           ->  Sort  (cost=35.65..35.65 rows=1 width=8) (actual time=0.241..0.241 rows=1 loops=553)
                 Sort Key: a.create_time
                 Sort Method:  quicksort  Memory: 25kB
                 ->  Nested Loop  (cost=1.25..35.64 rows=1 width=8) (actual time=0.105..0.221 rows=1 loops=553)
                       Join Filter: (a.article_type_id = art.id)
                       ->  Nested Loop  (cost=0.00..34.12 rows=4 width=12) (actual time=0.050..0.119 rows=5 loops=553)
                             Join Filter: (a.article_sender_type_id = ast.id)
                             ->  Seq Scan on article_sender_type ast  (cost=0.00..1.04 rows=1 width=4) (actual time=0.007..0.010 rows=1 loops=553)
                                   Filter: ((name)::text = 'agent'::text)
                             ->  Index Scan using article_ticket_id on article a  (cost=0.00..32.94 rows=11 width=16) (actual time=0.026..0.081 rows=8 loops=553)
                                   Index Cond: (a.ticket_id = $1)
                       ->  Materialize  (cost=1.25..1.28 rows=3 width=4) (actual time=0.001..0.008 rows=5 loops=2883)
                             ->  Seq Scan on article_type art  (cost=0.00..1.25 rows=3 width=4) (actual time=0.021..0.039 rows=5 loops=1)
                                   Filter: (((name)::text ~~ 'email-ext%'::text) OR ((name)::text ~~ 'note-ext%'::text) OR ((name)::text = 'phone'::text) OR ((name)::text = 'fax'::text) OR ((name)::text = 'sms'::text))
   SubPlan 8
     ->  Aggregate  (cost=327.28..327.29 rows=1 width=8) (actual time=0.388..0.389 rows=1 loops=553)
           ->  Hash Join  (cost=1.50..327.04 rows=95 width=8) (actual time=0.081..0.329 rows=43 loops=553)
                 Hash Cond: (h.state_id = s.id)
                 ->  Index Scan using ticket_history_ticket_id on ticket_history h  (cost=0.00..324.19 rows=105 width=12) (actual time=0.013..0.148 rows=77 loops=553)
                       Index Cond: (ticket_id = $1)
                 ->  Hash  (cost=1.26..1.26 rows=19 width=4) (actual time=0.078..0.078 rows=19 loops=1)
                       ->  Seq Scan on ticket_state s  (cost=0.00..1.26 rows=19 width=4) (actual time=0.011..0.044 rows=19 loops=1)
                             Filter: ((name)::text <> ALL ('{new,"open no response"}'::text[]))
 Total runtime: 54025.824 ms
(68 rows)

Per node type stats

node type	count	sum of times	% of query
Aggregate	2	89.586 ms	0.2 %
Append	1	4764.648 ms	8.8 %
Hash	3	68.065 ms	0.1 %
Hash Join	2	516.010 ms	1.0 %
Hash Semi Join	1	111.053 ms	0.2 %
Index Scan	5	361.109 ms	0.7 %
Index Scan Backward	2	51368.677 ms	95.1 %
Limit	3	346.362 ms	0.6 %
Materialize	1	23.025 ms	0.0 %
Nested Loop	2	48.826 ms	0.1 %
Result	2	512.907 ms	0.9 %
Seq Scan	6	20.924 ms	0.0 %
Sort	3	312.445 ms	0.6 %
Unique	1	117.236 ms	0.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
article	1	44.793 ms	0.1 %
Index Scan	1	44.793 ms	100.0 %
article_sender_type	1	5.530 ms	0.0 %
Seq Scan	1	5.530 ms	100.0 %
article_type	1	0.039 ms	0.0 %
Seq Scan	1	0.039 ms	100.0 %
ticket	1	15.223 ms	0.0 %
Seq Scan	1	15.223 ms	100.0 %
ticket_history	6	51684.993 ms	95.7 %
Index Scan	4	316.316 ms	0.6 %
Index Scan Backward	2	51368.677 ms	99.4 %
ticket_state	2	0.116 ms	0.0 %
Seq Scan	2	0.116 ms	100.0 %
ticket_state_type	1	0.016 ms	0.0 %
Seq Scan	1	0.016 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: mXx

Colorize:

Columns visibility:

Per node type stats

Per table stats