Result: 9mT0

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
852.111	7468.182	↑ 23.8	518	1	→ HashAggregate (cost=514702.37..514825.79 rows=12342 width=26) (actual time=7468.064..7468.182 rows=518 loops=1)
1034.845	6616.071	↑ 1.0	2301474	1	→ Hash Join (cost=32401.96..497358.65 rows=2312495 width=26) (actual time=366.588..6616.071 rows=2301474 loops=1) Hash Cond: (a11.selection_id = a13.selection_id)
3353.970	5362.445	↑ 1.0	2301474	1	→ Hash Join (cost=14163.32..424198.26 rows=2312495 width=34) (actual time=147.673..5362.445 rows=2301474 loops=1) Hash Cond: ((timezone('Europe/Stockholm'::text, a11.created_at))::date = a14.day_id)
1158.059	2008.273	↑ 1.0	2301474	1	→ Hash Join (cost=14146.02..386602.92 rows=2312495 width=38) (actual time=147.431..2008.273 rows=2301474 loops=1) Hash Cond: (a11.market_id = a12.market_id)
702.997	702.997	↑ 1.0	2301474	1	→ Index Scan using placement_parts_settled_timezone_idx on placement_parts_settled a11 (cost=0.01..329097.63 rows=2312495 width=20) (actual time=0.027..702.997 rows=2301474 loops=1) Index Cond: (((timezone('Europe/Stockholm'::text, created_at))::date >= to_date('2012-01-01'::text, 'YYYY-MM-DD'::text)) AND ((timezone('Europe/Stockholm'::text, created_at))::date <= to_date('2012-01-31'::text,
78.004	147.217	↓ 1.0	339979	1	→ Hash (cost=9896.56..9896.56 rows=339956 width=26) (actual time=147.217..147.217 rows=339979 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 19988kB
69.213	69.213	↓ 1.0	339979	1	→ Seq Scan on markets a12 (cost=0.00..9896.56 rows=339956 width=26) (actual time=0.004..69.213 rows=339979 loops=1)
0.121	0.202	↑ 1.0	502	1	→ Hash (cost=11.02..11.02 rows=502 width=8) (actual time=0.202..0.202 rows=502 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 20kB
0.081	0.081	↑ 1.0	502	1	→ Seq Scan on dim_day a14 (cost=0.00..11.02 rows=502 width=8) (actual time=0.003..0.081 rows=502 loops=1)
132.319	218.781	↓ 1.0	572716	1	→ Hash (cost=11079.95..11079.95 rows=572695 width=8) (actual time=218.781..218.781 rows=572716 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 22372kB
86.462	86.462	↓ 1.0	572716	1	→ Seq Scan on selections a13 (cost=0.00..11079.95 rows=572695 width=8) (actual time=0.006..86.462 rows=572716 loops=1)

 HashAggregate  (cost=514702.37..514825.79 rows=12342 width=26) (actual time=7468.064..7468.182 rows=518 loops=1)
   ->  Hash Join  (cost=32401.96..497358.65 rows=2312495 width=26) (actual time=366.588..6616.071 rows=2301474 loops=1)
         Hash Cond: (a11.selection_id = a13.selection_id)
         ->  Hash Join  (cost=14163.32..424198.26 rows=2312495 width=34) (actual time=147.673..5362.445 rows=2301474 loops=1)
               Hash Cond: ((timezone('Europe/Stockholm'::text, a11.created_at))::date = a14.day_id)
               ->  Hash Join  (cost=14146.02..386602.92 rows=2312495 width=38) (actual time=147.431..2008.273 rows=2301474 loops=1)
                     Hash Cond: (a11.market_id = a12.market_id)
                     ->  Index Scan using placement_parts_settled_timezone_idx on placement_parts_settled a11  (cost=0.01..329097.63 rows=2312495 width=20) (actual time=0.027..702.997 rows=2301474 loops=1)
                           Index Cond: (((timezone('Europe/Stockholm'::text, created_at))::date >= to_date('2012-01-01'::text, 'YYYY-MM-DD'::text)) AND ((timezone('Europe/Stockholm'::text, created_at))::date <= to_date('2012-01-31'::text,
 'YYYY-MM-DD'::text)))
                     ->  Hash  (cost=9896.56..9896.56 rows=339956 width=26) (actual time=147.217..147.217 rows=339979 loops=1)
                           Buckets: 65536  Batches: 1  Memory Usage: 19988kB
                           ->  Seq Scan on markets a12  (cost=0.00..9896.56 rows=339956 width=26) (actual time=0.004..69.213 rows=339979 loops=1)
               ->  Hash  (cost=11.02..11.02 rows=502 width=8) (actual time=0.202..0.202 rows=502 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 20kB
                     ->  Seq Scan on dim_day a14  (cost=0.00..11.02 rows=502 width=8) (actual time=0.003..0.081 rows=502 loops=1)
         ->  Hash  (cost=11079.95..11079.95 rows=572695 width=8) (actual time=218.781..218.781 rows=572716 loops=1)
               Buckets: 65536  Batches: 1  Memory Usage: 22372kB
               ->  Seq Scan on selections a13  (cost=0.00..11079.95 rows=572695 width=8) (actual time=0.006..86.462 rows=572716 loops=1)
 Total runtime: 7468.291 ms

Per node type stats

node type	count	sum of times	% of query
Hash	3	210.444 ms	2.8 %
Hash Join	3	5546.874 ms	74.3 %
HashAggregate	1	852.111 ms	11.4 %
Index Scan	1	702.997 ms	9.4 %
Seq Scan	3	155.756 ms	2.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
dim_day	1	0.081 ms	0.0 %
Seq Scan	1	0.081 ms	100.0 %
markets	1	69.213 ms	0.9 %
Seq Scan	1	69.213 ms	100.0 %
placement_parts_settled	1	702.997 ms	9.4 %
Index Scan	1	702.997 ms	100.0 %
selections	1	86.462 ms	1.2 %
Seq Scan	1	86.462 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 9mT0

Colorize:

Columns visibility:

Per node type stats

Per table stats