explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lxnm

Settings
# exclusive inclusive rows x rows loops node
1. 3,582.303 366,234.006 ↑ 17,311.3 9,647,483 1

Hash Join (cost=17,621,619.83..476,025,347.50 rows=167,010,425,000 width=261) (actual time=355,601.840..366,234.006 rows=9,647,483 loops=1)

  • Hash Cond: ((rd.source_deal_id)::text = (dd.source_deal_id)::text)
2.          

CTE stage_spot_deals

3. 225,323.314 248,995.209 ↑ 1.0 17,674,446 1

Hash Left Join (cost=1,205,454.34..6,725,820.65 rows=17,674,446 width=236) (actual time=9,466.250..248,995.209 rows=17,674,446 loops=1)

  • Hash Cond: (os.order_id = lh_order_to_deal.order_id)
4. 11,497.771 23,602.556 ↑ 1.0 17,674,446 1

Hash Right Join (cost=1,198,341.03..1,849,171.21 rows=17,674,446 width=23) (actual time=9,394.687..23,602.556 rows=17,674,446 loops=1)

  • Hash Cond: (lh_spot_to_deal.usn = os.spot_id)
5. 2,718.586 2,718.586 ↓ 1.0 5,612,137 1

Seq Scan on lh_spot_to_deal (cost=0.00..302,622.70 rows=5,605,985 width=17) (actual time=0.128..2,718.586 rows=5,612,137 loops=1)

  • Filter: (row_num = 1)
  • Rows Removed by Filter: 1647706
6. 4,606.183 9,386.199 ↑ 1.0 17,674,446 1

Hash (cost=891,108.46..891,108.46 rows=17,674,446 width=10) (actual time=9,386.199..9,386.199 rows=17,674,446 loops=1)

  • Buckets: 131072 Batches: 512 Memory Usage: 2495kB
7. 4,780.016 4,780.016 ↑ 1.0 17,674,446 1

Seq Scan on order_spot os (cost=0.00..891,108.46 rows=17,674,446 width=10) (actual time=0.129..4,780.016 rows=17,674,446 loops=1)

8. 35.800 69.339 ↑ 1.0 158,947 1

Hash (cost=4,349.47..4,349.47 rows=158,947 width=16) (actual time=69.339..69.339 rows=158,947 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2968kB
9. 33.539 33.539 ↑ 1.0 158,947 1

Seq Scan on lh_order_to_deal (cost=0.00..4,349.47 rows=158,947 width=16) (actual time=0.133..33.539 rows=158,947 loops=1)

10.          

CTE stage_order_deals

11. 15,320.678 264,380.792 ↓ 1,082.5 216,501 1

HashAggregate (cost=441,861.15..441,863.15 rows=200 width=36) (actual time=264,271.141..264,380.792 rows=216,501 loops=1)

  • Group Key: os_1.order_id
12. 249,060.114 249,060.114 ↑ 1.0 17,674,446 1

CTE Scan on stage_spot_deals os_1 (cost=0.00..353,488.92 rows=17,674,446 width=222) (actual time=0.000..249,060.114 rows=17,674,446 loops=1)

13. 4,432.516 361,910.802 ↑ 102.4 17,630,251 1

Merge Right Join (cost=10,445,842.19..46,584,986.14 rows=1,804,737,681 width=303) (actual time=349,379.951..361,910.802 rows=17,630,251 loops=1)

  • Merge Cond: ((((d.deal_id)::character varying)::text) = (COALESCE(od.deal_id, (ds.deal_id)::text)))
14. 83.342 422.685 ↑ 1.0 20,422 1

Sort (cost=3,213.96..3,265.02 rows=20,422 width=14) (actual time=418.214..422.685 rows=20,422 loops=1)

  • Sort Key: (((d.deal_id)::character varying)::text)
  • Sort Method: quicksort Memory: 2364kB
15. 9.127 339.343 ↑ 1.0 20,422 1

Hash Right Join (cost=793.89..1,751.97 rows=20,422 width=14) (actual time=72.791..339.343 rows=20,422 loops=1)

  • Hash Cond: (proposal.deal_id = d.deal_id)
16. 257.731 257.731 ↑ 1.0 20,422 1

Seq Scan on proposal (cost=0.00..677.27 rows=20,422 width=14) (actual time=0.121..257.731 rows=20,422 loops=1)

  • Filter: ((proposal_status_code)::text <> 'OPTIONS'::text)
17. 4.073 72.485 ↑ 1.0 20,422 1

Hash (cost=538.62..538.62 rows=20,422 width=4) (actual time=72.484..72.485 rows=20,422 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 974kB
18. 68.412 68.412 ↑ 1.0 20,422 1

Index Only Scan using idx_etv_deal_id on deal d (cost=0.29..538.62 rows=20,422 width=4) (actual time=0.037..68.412 rows=20,422 loops=1)

  • Heap Fetches: 0
19. 3,158.228 357,055.601 ↑ 1.0 17,630,251 1

Materialize (cost=10,442,628.22..10,531,000.45 rows=17,674,446 width=293) (actual time=348,961.729..357,055.601 rows=17,630,251 loops=1)

20. 62,693.984 353,897.373 ↑ 1.0 17,630,251 1

Sort (cost=10,442,628.22..10,486,814.34 rows=17,674,446 width=293) (actual time=348,961.723..353,897.373 rows=17,630,251 loops=1)

  • Sort Key: (COALESCE(od.deal_id, (ds.deal_id)::text))
  • Sort Method: external merge Disk: 1355968kB
21. 5,900.638 291,203.389 ↑ 1.0 17,630,251 1

Hash Join (cost=860.93..1,065,758.00 rows=17,674,446 width=293) (actual time=273,990.016..291,203.389 rows=17,630,251 loops=1)

  • Hash Cond: (((COALESCE(od.deal_id, (ds.deal_id)::text))::character varying(100))::text = (rd.source_deal_id)::text)
22. 9,159.568 285,295.350 ↑ 1.0 17,674,446 1

Hash Left Join (cost=6.50..1,016,287.14 rows=17,674,446 width=268) (actual time=273,982.564..285,295.350 rows=17,674,446 loops=1)

  • Hash Cond: (ds.order_id = od.order_id)
23. 11,619.479 11,619.479 ↑ 1.0 17,674,446 1

CTE Scan on stage_spot_deals ds (cost=0.00..353,488.92 rows=17,674,446 width=236) (actual time=9,466.253..11,619.479 rows=17,674,446 loops=1)

24. 54.670 264,516.303 ↓ 1,082.5 216,501 1

Hash (cost=4.00..4.00 rows=200 width=36) (actual time=264,516.303..264,516.303 rows=216,501 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3527kB
25. 264,461.633 264,461.633 ↓ 1,082.5 216,501 1

CTE Scan on stage_order_deals od (cost=0.00..4.00 rows=200 width=36) (actual time=264,271.145..264,461.633 rows=216,501 loops=1)

26. 3.025 7.401 ↓ 1.0 18,509 1

Hash (cost=623.08..623.08 rows=18,508 width=25) (actual time=7.401..7.401 rows=18,509 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1307kB
27. 4.376 4.376 ↓ 1.0 18,509 1

Seq Scan on dim_deal rd (cost=0.00..623.08 rows=18,508 width=25) (actual time=0.121..4.376 rows=18,509 loops=1)

28. 4.794 740.901 ↑ 1.0 18,469 1

Hash (cost=7,862.49..7,862.49 rows=18,508 width=14) (actual time=740.901..740.901 rows=18,469 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1092kB
29. 2.196 736.107 ↑ 1.0 18,469 1

Subquery Scan on dd (cost=7,492.33..7,862.49 rows=18,508 width=14) (actual time=728.924..736.107 rows=18,469 loops=1)

30. 43.094 733.911 ↑ 1.0 18,469 1

HashAggregate (cost=7,492.33..7,677.41 rows=18,508 width=14) (actual time=728.923..733.911 rows=18,469 loops=1)

  • Group Key: dd_1.source_deal_id
31. 44.376 690.817 ↑ 1.0 149,209 1

Hash Join (cost=854.43..6,746.28 rows=149,209 width=14) (actual time=230.013..690.817 rows=149,209 loops=1)

  • Hash Cond: (dl.deal_sk = dd_1.deal_sk)
32. 416.764 416.764 ↑ 1.0 149,209 1

Seq Scan on fact_deal_line dl (cost=0.00..5,500.09 rows=149,209 width=10) (actual time=0.148..416.764 rows=149,209 loops=1)

33. 6.125 229.677 ↓ 1.0 18,509 1

Hash (cost=623.08..623.08 rows=18,508 width=20) (actual time=229.677..229.677 rows=18,509 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1242kB
34. 223.552 223.552 ↓ 1.0 18,509 1

Seq Scan on dim_deal dd_1 (cost=0.00..623.08 rows=18,508 width=20) (actual time=0.144..223.552 rows=18,509 loops=1)

Planning time : 173.275 ms
Execution time : 372,251.518 ms