explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JDsU

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Merge Join (cost=19,093,924.62..63,349,288.73 rows=1,964,704,244 width=259) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=395,249.66..7,576,120.79 rows=19,747,756 width=234) (actual rows= loops=)

  • Hash Cond: (os.order_id = lh_order_to_deal.order_id)
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=387,584.39..2,050,588.87 rows=19,747,756 width=34) (actual rows= loops=)

  • Hash Cond: (os.source_spot_id = lh_spot_to_deal.usn)
5. 0.000 0.000 ↓ 0.0

Seq Scan on blended_spots os (cost=0.00..1,134,359.56 rows=19,747,756 width=21) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Hash (cost=272,113.44..272,113.44 rows=6,289,436 width=17) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Seq Scan on lh_spot_to_deal (cost=0.00..272,113.44 rows=6,289,436 width=17) (actual rows= loops=)

  • Filter: (row_num = 1)
8. 0.000 0.000 ↓ 0.0

Hash (cost=4,690.79..4,690.79 rows=171,079 width=16) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on lh_order_to_deal (cost=0.00..4,690.79 rows=171,079 width=16) (actual rows= loops=)

10.          

CTE stage_order_deals

11. 0.000 0.000 ↓ 0.0

HashAggregate (cost=493,693.90..493,695.90 rows=200 width=36) (actual rows= loops=)

  • Group Key: os_1.order_id
12. 0.000 0.000 ↓ 0.0

CTE Scan on stage_spot_deals os_1 (cost=0.00..394,955.12 rows=19,747,756 width=222) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=3,461.82..3,511.56 rows=19,898 width=35) (actual rows= loops=)

  • Sort Key: dd.source_deal_id
14. 0.000 0.000 ↓ 0.0

Subquery Scan on dd (cost=1,643.11..2,041.07 rows=19,898 width=35) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,643.11..1,842.09 rows=19,898 width=35) (actual rows= loops=)

  • Group Key: dd_1.deal_sk
16. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,529.70..1,543.62 rows=19,898 width=35) (actual rows= loops=)

  • Hash Cond: (dl.deal_sk = dd_1.deal_sk)
17. 0.000 0.000 ↓ 0.0

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

18. 0.000 0.000 ↓ 0.0

Hash (cost=1,280.98..1,280.98 rows=19,898 width=33) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on dim_deal dd_1 (cost=0.00..1,280.98 rows=19,898 width=33) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Materialize (cost=11,020,646.11..11,119,384.89 rows=19,747,756 width=266) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=11,020,646.11..11,070,015.50 rows=19,747,756 width=266) (actual rows= loops=)

  • Sort Key: (((COALESCE(od.deal_id, (ds.deal_id)::text))::character varying(100))::text)
22. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=6.50..1,135,502.47 rows=19,747,756 width=266) (actual rows= loops=)

  • Hash Cond: (ds.order_id = od.order_id)
23. 0.000 0.000 ↓ 0.0

CTE Scan on stage_spot_deals ds (cost=0.00..394,955.12 rows=19,747,756 width=234) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=36) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

CTE Scan on stage_order_deals od (cost=0.00..4.00 rows=200 width=36) (actual rows= loops=)