explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aTZt

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.163 9,489.962 ↑ 1.0 200 1

Limit (cost=699,189.13..699,196.13 rows=200 width=52) (actual time=9,489.750..9,489.962 rows=200 loops=1)

2. 0.202 9,489.799 ↑ 200.0 200 1

WindowAgg (cost=699,189.13..700,589.13 rows=40,000 width=52) (actual time=9,489.748..9,489.799 rows=200 loops=1)

3. 0.148 9,489.597 ↑ 98.5 406 1

Group (cost=699,189.13..699,689.13 rows=40,000 width=48) (actual time=9,489.389..9,489.597 rows=406 loops=1)

  • Group Key: (COALESCE(name.sort_date_iso_0, mall_orders_count_metric.sort_date_iso_0)), (COALESCE(name.id, mall_orders_count_metric.id)), name.name, mall_orders_count_metric.mall_orders_count_metric
4.          

CTE name

5. 0.234 1,456.965 ↑ 14.4 406 1

Sort (cost=214,340.90..214,355.51 rows=5,844 width=40) (actual time=1,456.880..1,456.965 rows=406 loops=1)

  • Sort Key: dim_dates.date_iso
  • Sort Method: quicksort Memory: 56kB
6. 1.076 1,456.731 ↑ 14.4 406 1

Finalize HashAggregate (cost=213,916.83..213,975.27 rows=5,844 width=40) (actual time=1,456.629..1,456.731 rows=406 loops=1)

  • Group Key: dim_dates.date_iso, dim_dates.date_iso
7. 5.948 1,455.655 ↑ 12.3 2,842 1

Gather (cost=210,089.01..213,653.85 rows=35,064 width=40) (actual time=1,454.550..1,455.655 rows=2,842 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
8. 402.918 1,449.707 ↑ 14.4 406 7

Partial HashAggregate (cost=209,089.01..209,147.45 rows=5,844 width=40) (actual time=1,449.549..1,449.707 rows=406 loops=7)

  • Group Key: dim_dates.date_iso, dim_dates.date_iso
9. 540.361 1,046.789 ↑ 1.2 654,516 7

Hash Join (cost=204.49..203,362.22 rows=763,573 width=21) (actual time=5.198..1,046.789 rows=654,516 loops=7)

  • Hash Cond: (mall_orders_cz_dwh.order_date = dim_dates.date_iso)
10. 501.316 501.316 ↑ 1.2 654,516 7

Parallel Seq Scan on mall_orders_cz_dwh (cost=0.00..192,658.60 rows=763,573 width=4) (actual time=0.035..501.316 rows=654,516 loops=7)

  • Filter: ((order_date >= '2017-11-01'::date) AND (order_date <= '2018-12-11'::date))
11. 2.545 5.112 ↑ 1.0 5,844 7

Hash (cost=131.44..131.44 rows=5,844 width=17) (actual time=5.112..5.112 rows=5,844 loops=7)

  • Buckets: 8192 Batches: 1 Memory Usage: 344kB
12. 2.567 2.567 ↑ 1.0 5,844 7

Seq Scan on dim_dates (cost=0.00..131.44 rows=5,844 width=17) (actual time=0.016..2.567 rows=5,844 loops=7)

13.          

CTE mall_orders_count_metric

14. 0.220 8,031.452 ↑ 98.5 406 1

Sort (cost=476,933.77..477,033.77 rows=40,000 width=16) (actual time=8,031.386..8,031.452 rows=406 loops=1)

  • Sort Key: dim_dates_1.date_iso, dim_dates_1.date_iso
  • Sort Method: quicksort Memory: 44kB
15. 738.617 8,031.232 ↑ 98.5 406 1

HashAggregate (cost=473,476.23..473,876.23 rows=40,000 width=16) (actual time=8,031.080..8,031.232 rows=406 loops=1)

  • Group Key: dim_dates_1.date_iso, dim_dates_1.date_iso
16. 3,096.513 7,292.615 ↑ 1.7 2,661,592 1

HashAggregate (cost=347,486.65..393,301.04 rows=4,581,439 width=16) (actual time=6,311.860..7,292.615 rows=2,661,592 loops=1)

  • Group Key: dim_dates_1.date_iso, dim_dates_1.date_iso, mall_orders_cz_dwh_1.order_id
17. 1,808.587 4,196.102 ↓ 1.0 4,581,612 1

Hash Join (cost=204.49..313,125.86 rows=4,581,439 width=16) (actual time=2.460..4,196.102 rows=4,581,612 loops=1)

  • Hash Cond: (mall_orders_cz_dwh_1.order_date = dim_dates_1.date_iso)
18. 2,385.109 2,385.109 ↓ 1.0 4,581,612 1

Seq Scan on mall_orders_cz_dwh mall_orders_cz_dwh_1 (cost=0.00..249,926.58 rows=4,581,439 width=12) (actual time=0.019..2,385.109 rows=4,581,612 loops=1)

  • Filter: ((order_date >= '2017-11-01'::date) AND (order_date <= '2018-12-11'::date))
19. 1.210 2.406 ↑ 1.0 5,844 1

Hash (cost=131.44..131.44 rows=5,844 width=4) (actual time=2.406..2.406 rows=5,844 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 270kB
20. 1.196 1.196 ↑ 1.0 5,844 1

Seq Scan on dim_dates dim_dates_1 (cost=0.00..131.44 rows=5,844 width=4) (actual time=0.006..1.196 rows=5,844 loops=1)

21. 0.175 9,489.449 ↑ 98.5 406 1

Sort (cost=7,799.86..7,899.86 rows=40,000 width=48) (actual time=9,489.388..9,489.449 rows=406 loops=1)

  • Sort Key: (COALESCE(name.sort_date_iso_0, mall_orders_count_metric.sort_date_iso_0)) NULLS FIRST, (COALESCE(name.id, mall_orders_count_metric.id)), name.name, mall_orders_count_metric.mall_orders_count_metric
  • Sort Method: quicksort Memory: 56kB
22. 0.271 9,489.274 ↑ 98.5 406 1

Merge Full Join (cost=4,340.04..4,742.31 rows=40,000 width=48) (actual time=9,488.895..9,489.274 rows=406 loops=1)

  • Merge Cond: ((name.id = mall_orders_count_metric.id) AND (name.sort_date_iso_0 = mall_orders_count_metric.sort_date_iso_0))
23. 0.153 1,457.271 ↑ 14.4 406 1

Sort (cost=482.50..497.11 rows=5,844 width=40) (actual time=1,457.212..1,457.271 rows=406 loops=1)

  • Sort Key: name.id, name.sort_date_iso_0
  • Sort Method: quicksort Memory: 56kB
24. 1,457.118 1,457.118 ↑ 14.4 406 1

CTE Scan on name (cost=0.00..116.88 rows=5,844 width=40) (actual time=1,456.884..1,457.118 rows=406 loops=1)

25. 0.159 8,031.732 ↑ 98.5 406 1

Sort (cost=3,857.54..3,957.54 rows=40,000 width=16) (actual time=8,031.676..8,031.732 rows=406 loops=1)

  • Sort Key: mall_orders_count_metric.id, mall_orders_count_metric.sort_date_iso_0
  • Sort Method: quicksort Memory: 44kB
26. 8,031.573 8,031.573 ↑ 98.5 406 1

CTE Scan on mall_orders_count_metric (cost=0.00..800.00 rows=40,000 width=16) (actual time=8,031.391..8,031.573 rows=406 loops=1)

Planning time : 0.736 ms
Execution time : 9,510.741 ms