#	exclusive	inclusive	rows x	rows	loops	node
1.	423.768	423.768	↓ 3.0	3	1	→ CTE Scan on temp_2 (cost=15560.22..15560.40 rows=1 width=192) (actual time=334.630..423.768 rows=3 loops=1) Filter: (is_loss = 0)
2.						CTE temp_1
3.	80.559	247.912	↓ 1.0	4725	1	→ WindowAgg (cost=14037.59..14209.90 rows=4595 width=67) (actual time=105.700..247.912 rows=4725 loops=1)
4.	60.646	167.353	↓ 1.0	4725	1	→ WindowAgg (cost=14037.59..14129.49 rows=4595 width=67) (actual time=105.551..167.353 rows=4725 loops=1)
5.	86.606	106.707	↓ 1.0	4725	1	→ Sort (cost=14037.59..14049.07 rows=4595 width=67) (actual time=105.494..106.707 rows=4725 loops=1) Sort Key: i.email, (date_trunc('hour'::text, i.date)) Sort Method: quicksort Memory: 117292kB
6.	9.954	20.101	↓ 1.0	4725	1	→ WindowAgg (cost=13677.66..13758.07 rows=4595 width=67) (actual time=9.725..20.101 rows=4725 loops=1)
7.	2.962	10.147	↓ 1.0	4725	1	→ Sort (cost=13677.66..13689.15 rows=4595 width=67) (actual time=9.704..10.147 rows=4725 loops=1) Sort Key: i.telno Sort Method: quicksort Memory: 846kB
8.	6.443	7.185	↓ 1.0	4725	1	→ Bitmap Heap Scan on alldat_dk i (cost=151.56..13398.15 rows=4595 width=67) (actual time=1.082..7.185 rows=4725 loops=1) Recheck Cond: ((date >= '2011-11-01 00:00:00'::timestamp without time zone) AND (date <= '2011-11-03 00:00:00'::timestamp without time zone))
9.	0.742	0.742	↓ 1.0	4815	1	→ Bitmap Index Scan on idx_alldat_dk_date (cost=0.00..150.41 rows=4595 width=0) (actual time=0.742..0.742 rows=4815 loops=1) Index Cond: ((date >= '2011-11-01 00:00:00'::timestamp without time zone) AND (date <= '2011-11-03 00:00:00'::timestamp without time zone))
10.						CTE temp_2
11.	78.305	423.648	↑ 1.6	5	1	→ Nested Loop (cost=0.00..1350.32 rows=8 width=184) (actual time=212.127..423.648 rows=5 loops=1)
12.	345.178	345.178	↓ 1.9	15	1	→ CTE Scan on temp_1 (cost=0.00..1275.11 rows=8 width=168) (actual time=162.163..345.178 rows=15 loops=1) Filter: ((email IS NOT NULL) AND (type = 2) AND (array_upper(array_unique(pnos), 1) > 1))
13.	0.165	0.165	↓ 0.0	0	15	→ Index Scan using pe_balance_dk_pkey on pe_balance_dk (cost=0.00..8.38 rows=1 width=24) (actual time=0.010..0.011 rows=0 loops=15) Index Cond: (id = temp_1.id)

"CTE Scan on temp_2  (cost=15560.22..15560.40 rows=1 width=192) (actual time=334.630..423.768 rows=3 loops=1)"
"  Filter: (is_loss = 0)"
"  CTE temp_1"
"    ->  WindowAgg  (cost=14037.59..14209.90 rows=4595 width=67) (actual time=105.700..247.912 rows=4725 loops=1)"
"          ->  WindowAgg  (cost=14037.59..14129.49 rows=4595 width=67) (actual time=105.551..167.353 rows=4725 loops=1)"
"                ->  Sort  (cost=14037.59..14049.07 rows=4595 width=67) (actual time=105.494..106.707 rows=4725 loops=1)"
"                      Sort Key: i.email, (date_trunc('hour'::text, i.date))"
"                      Sort Method: quicksort  Memory: 117292kB"
"                      ->  WindowAgg  (cost=13677.66..13758.07 rows=4595 width=67) (actual time=9.725..20.101 rows=4725 loops=1)"
"                            ->  Sort  (cost=13677.66..13689.15 rows=4595 width=67) (actual time=9.704..10.147 rows=4725 loops=1)"
"                                  Sort Key: i.telno"
"                                  Sort Method: quicksort  Memory: 846kB"
"                                  ->  Bitmap Heap Scan on alldat_dk i  (cost=151.56..13398.15 rows=4595 width=67) (actual time=1.082..7.185 rows=4725 loops=1)"
"                                        Recheck Cond: ((date >= '2011-11-01 00:00:00'::timestamp without time zone) AND (date <= '2011-11-03 00:00:00'::timestamp without time zone))"
"                                        ->  Bitmap Index Scan on idx_alldat_dk_date  (cost=0.00..150.41 rows=4595 width=0) (actual time=0.742..0.742 rows=4815 loops=1)"
"                                              Index Cond: ((date >= '2011-11-01 00:00:00'::timestamp without time zone) AND (date <= '2011-11-03 00:00:00'::timestamp without time zone))"
"  CTE temp_2"
"    ->  Nested Loop  (cost=0.00..1350.32 rows=8 width=184) (actual time=212.127..423.648 rows=5 loops=1)"
"          ->  CTE Scan on temp_1  (cost=0.00..1275.11 rows=8 width=168) (actual time=162.163..345.178 rows=15 loops=1)"
"                Filter: ((email IS NOT NULL) AND (type = 2) AND (array_upper(array_unique(pnos), 1) > 1))"
"          ->  Index Scan using pe_balance_dk_pkey on pe_balance_dk  (cost=0.00..8.38 rows=1 width=24) (actual time=0.010..0.011 rows=0 loops=15)"
"                Index Cond: (id = temp_1.id)"
"Total runtime: 516.172 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	6.443 ms	1.5 %
Bitmap Index Scan	1	0.742 ms	0.2 %
CTE Scan	2	768.946 ms	181.5 %
Index Scan	1	0.165 ms	0.0 %
Nested Loop	1	78.305 ms	18.5 %
Sort	2	89.568 ms	21.1 %
WindowAgg	3	151.159 ms	35.7 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
alldat_dk	1	6.443 ms	1.5 %
Bitmap Heap Scan	1	6.443 ms	100.0 %
pe_balance_dk	1	0.165 ms	0.0 %
Index Scan	1	0.165 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: WSk

Colorize:

Columns visibility:

Per node type stats

Per table stats