#	exclusive	inclusive	rows x	rows	loops	node
1.	0.207	7,716.274	↑ 98.5	406	1	→ Sort (cost=476,967.17..477,067.17 rows=40,000 width=16) (actual time=7,716.222..7,716.274 rows=406 loops=1) Sort Key: dim_dates.date_iso, dim_dates.date_iso Sort Method: quicksort Memory: 44kB
2.	741.146	7,716.067	↑ 98.5	406	1	→ HashAggregate (cost=473,509.63..473,909.63 rows=40,000 width=16) (actual time=7,715.906..7,716.067 rows=406 loops=1) Group Key: dim_dates.date_iso, dim_dates.date_iso
3.	2,927.463	6,974.921	↑ 1.7	2,661,592	1	→ HashAggregate (cost=347,505.65..393,325.28 rows=4,581,963 width=16) (actual time=6,005.922..6,974.921 rows=2,661,592 loops=1) Group Key: dim_dates.date_iso, dim_dates.date_iso, mall_orders_cz_dwh.order_id
4.	1,799.335	4,047.458	↑ 1.0	4,581,612	1	→ Hash Join (cost=204.49..313,140.93 rows=4,581,963 width=16) (actual time=2.514..4,047.458 rows=4,581,612 loops=1) Hash Cond: (mall_orders_cz_dwh.order_date = dim_dates.date_iso)
5.	2,245.710	2,245.710	↑ 1.0	4,581,612	1	→ Seq Scan on mall_orders_cz_dwh (cost=0.00..249,934.45 rows=4,581,963 width=12) (actual time=0.059..2,245.710 rows=4,581,612 loops=1) Filter: ((order_date >= '2017-11-01'::date) AND (order_date <= '2018-12-11'::date))
6.	1.196	2.413	↑ 1.0	5,844	1	→ Hash (cost=131.44..131.44 rows=5,844 width=4) (actual time=2.413..2.413 rows=5,844 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 270kB
7.	1.217	1.217	↑ 1.0	5,844	1	→ Seq Scan on dim_dates (cost=0.00..131.44 rows=5,844 width=4) (actual time=0.009..1.217 rows=5,844 loops=1)

                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=476967.17..477067.17 rows=40000 width=16) (actual time=7716.222..7716.274 rows=406 loops=1)
   Sort Key: dim_dates.date_iso, dim_dates.date_iso
   Sort Method: quicksort  Memory: 44kB
   ->  HashAggregate  (cost=473509.63..473909.63 rows=40000 width=16) (actual time=7715.906..7716.067 rows=406 loops=1)
         Group Key: dim_dates.date_iso, dim_dates.date_iso
         ->  HashAggregate  (cost=347505.65..393325.28 rows=4581963 width=16) (actual time=6005.922..6974.921 rows=2661592 loops=1)
               Group Key: dim_dates.date_iso, dim_dates.date_iso, mall_orders_cz_dwh.order_id
               ->  Hash Join  (cost=204.49..313140.93 rows=4581963 width=16) (actual time=2.514..4047.458 rows=4581612 loops=1)
                     Hash Cond: (mall_orders_cz_dwh.order_date = dim_dates.date_iso)
                     ->  Seq Scan on mall_orders_cz_dwh  (cost=0.00..249934.45 rows=4581963 width=12) (actual time=0.059..2245.710 rows=4581612 loops=1)
                           Filter: ((order_date >= '2017-11-01'::date) AND (order_date <= '2018-12-11'::date))
                     ->  Hash  (cost=131.44..131.44 rows=5844 width=4) (actual time=2.413..2.413 rows=5844 loops=1)
                           Buckets: 8192  Batches: 1  Memory Usage: 270kB
                           ->  Seq Scan on dim_dates  (cost=0.00..131.44 rows=5844 width=4) (actual time=0.009..1.217 rows=5844 loops=1)
 Planning time: 0.308 ms
 Execution time: 7736.748 ms
(16 rows)

Per node type stats

node type	count	sum of times	% of query
Hash	1	1.196 ms	0.0 %
Hash Join	1	1,799.335 ms	23.3 %
HashAggregate	2	3,668.609 ms	47.5 %
Seq Scan	2	2,246.927 ms	29.1 %
Sort	1	0.207 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
dim_dates	1	1.217 ms	0.0 %
Seq Scan	1	1.217 ms	100.0 %
mall_orders_cz_dwh	1	2,245.710 ms	29.1 %
Seq Scan	1	2,245.710 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LXDo : Optimization for: plan #aTZt

Color mode:

Visible columns:

Optimization path:

Per node type stats

Per table stats