explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0rAB

Settings
# exclusive inclusive rows x rows loops node
1. 3,605.643 541,165.825 ↑ 180.9 11,041,525 1

Merge Join (cost=19,274,890.78..64,265,829.82 rows=1,997,375,844 width=259) (actual time=530,896.326..541,165.825 rows=11,041,525 loops=1)

  • Merge Cond: ((dd.source_deal_id)::text = (((COALESCE(od.deal_id, (ds.deal_id)::text))::character varying(100))::text))
2.          

CTE stage_spot_deals

3. 425,343.348 449,689.673 ↑ 1.0 19,929,907 1

Hash Left Join (cost=399,766.52..7,647,759.61 rows=19,932,896 width=234) (actual time=8,917.252..449,689.673 rows=19,929,907 loops=1)

  • Hash Cond: (os.order_id = lh_order_to_deal.order_id)
4. 10,552.239 24,001.184 ↑ 1.0 19,929,907 1

Hash Left Join (cost=392,058.55..2,070,457.32 rows=19,932,896 width=34) (actual time=8,571.012..24,001.184 rows=19,929,907 loops=1)

  • Hash Cond: (os.source_spot_id = lh_spot_to_deal.usn)
5. 4,878.341 4,878.341 ↑ 1.0 19,929,907 1

Seq Scan on blended_spots os (cost=0.00..1,144,445.96 rows=19,932,896 width=21) (actual time=0.122..4,878.341 rows=19,929,907 loops=1)

6. 1,705.241 8,570.604 ↑ 1.0 6,367,890 1

Hash (cost=275,098.24..275,098.24 rows=6,370,585 width=17) (actual time=8,570.604..8,570.604 rows=6,367,890 loops=1)

  • Buckets: 65,536 Batches: 128 Memory Usage: 2,970kB
7. 6,865.363 6,865.363 ↑ 1.0 6,367,890 1

Seq Scan on lh_spot_to_deal (cost=0.00..275,098.24 rows=6,370,585 width=17) (actual time=6.795..6,865.363 rows=6,367,890 loops=1)

  • Filter: (row_num = 1)
  • Rows Removed by Filter: 1,016
8. 34.339 345.141 ↑ 1.0 172,043 1

Hash (cost=4,716.43..4,716.43 rows=172,043 width=16) (actual time=345.140..345.141 rows=172,043 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,135kB
9. 310.802 310.802 ↑ 1.0 172,043 1

Seq Scan on lh_order_to_deal (cost=0.00..4,716.43 rows=172,043 width=16) (actual time=21.438..310.802 rows=172,043 loops=1)

10.          

CTE stage_order_deals

11. 13,259.432 465,554.678 ↓ 1,185.2 237,039 1

HashAggregate (cost=498,322.40..498,324.40 rows=200 width=36) (actual time=465,468.203..465,554.678 rows=237,039 loops=1)

  • Group Key: os_1.order_id
12. 452,295.246 452,295.246 ↑ 1.0 19,929,907 1

CTE Scan on stage_spot_deals os_1 (cost=0.00..398,657.92 rows=19,932,896 width=222) (actual time=0.000..452,295.246 rows=19,929,907 loops=1)

13. 75.901 99.456 ↑ 1.0 20,041 1

Sort (cost=3,493.86..3,543.96 rows=20,041 width=34) (actual time=92.750..99.456 rows=20,041 loops=1)

  • Sort Key: dd.source_deal_id
  • Sort Method: quicksort Memory: 2,452kB
14. 1.714 23.555 ↑ 1.0 20,041 1

Subquery Scan on dd (cost=1,661.04..2,061.86 rows=20,041 width=34) (actual time=18.696..23.555 rows=20,041 loops=1)

15. 9.578 21.841 ↑ 1.0 20,041 1

HashAggregate (cost=1,661.04..1,861.45 rows=20,041 width=34) (actual time=18.696..21.841 rows=20,041 loops=1)

  • Group Key: dd_1.deal_sk
16. 2.526 12.263 ↑ 1.0 20,041 1

Hash Right Join (cost=1,546.92..1,560.84 rows=20,041 width=34) (actual time=9.874..12.263 rows=20,041 loops=1)

  • Hash Cond: (dl.deal_sk = dd_1.deal_sk)
17. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on fact_deal_line dl (cost=0.00..13.10 rows=310 width=10) (actual time=0.003..0.003 rows=0 loops=1)

18. 4.154 9.734 ↑ 1.0 20,041 1

Hash (cost=1,296.41..1,296.41 rows=20,041 width=32) (actual time=9.734..9.734 rows=20,041 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,570kB
19. 5.580 5.580 ↑ 1.0 20,041 1

Seq Scan on dim_deal dd_1 (cost=0.00..1,296.41 rows=20,041 width=32) (actual time=0.104..5.580 rows=20,041 loops=1)

20. 3,008.221 537,460.726 ↑ 1.8 11,099,167 1

Materialize (cost=11,125,312.91..11,224,977.39 rows=19,932,896 width=266) (actual time=530,803.563..537,460.726 rows=11,099,167 loops=1)

21. 49,180.557 534,452.505 ↑ 1.8 11,099,167 1

Sort (cost=11,125,312.91..11,175,145.15 rows=19,932,896 width=266) (actual time=530,803.550..534,452.505 rows=11,099,167 loops=1)

  • Sort Key: (((COALESCE(od.deal_id, (ds.deal_id)::text))::character varying(100))::text)
  • Sort Method: external merge Disk: 972,672kB
22. 8,486.790 485,271.948 ↑ 1.0 19,929,907 1

Hash Left Join (cost=6.50..1,146,148.02 rows=19,932,896 width=266) (actual time=474,593.320..485,271.948 rows=19,929,907 loops=1)

  • Hash Cond: (ds.order_id = od.order_id)
23. 11,109.099 11,109.099 ↑ 1.0 19,929,907 1

CTE Scan on stage_spot_deals ds (cost=0.00..398,657.92 rows=19,932,896 width=234) (actual time=8,917.254..11,109.099 rows=19,929,907 loops=1)

24. 46.483 465,676.059 ↓ 1,185.2 237,039 1

Hash (cost=4.00..4.00 rows=200 width=36) (actual time=465,676.059..465,676.059 rows=237,039 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3,710kB
25. 465,629.576 465,629.576 ↓ 1,185.2 237,039 1

CTE Scan on stage_order_deals od (cost=0.00..4.00 rows=200 width=36) (actual time=465,468.206..465,629.576 rows=237,039 loops=1)

Planning time : 32.946 ms
Execution time : 574,625.831 ms