explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ECNv

Settings
# exclusive inclusive rows x rows loops node
1. 3,570.757 392,866.059 ↑ 179.0 10,976,509 1

Merge Join (cost=19,093,924.62..63,349,288.73 rows=1,964,704,244 width=259) (actual time=382,672.706..392,866.059 rows=10,976,509 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. 280,589.915 299,407.235 ↓ 1.0 19,753,194 1

Hash Left Join (cost=395,249.66..7,576,120.79 rows=19,747,756 width=234) (actual time=3,392.022..299,407.235 rows=19,753,194 loops=1)

  • Hash Cond: (os.order_id = lh_order_to_deal.order_id)
4. 10,531.053 18,756.873 ↓ 1.0 19,753,194 1

Hash Left Join (cost=387,584.39..2,050,588.87 rows=19,747,756 width=34) (actual time=3,330.593..18,756.873 rows=19,753,194 loops=1)

  • Hash Cond: (os.source_spot_id = lh_spot_to_deal.usn)
5. 4,895.600 4,895.600 ↓ 1.0 19,753,194 1

Seq Scan on blended_spots os (cost=0.00..1,134,359.56 rows=19,747,756 width=21) (actual time=0.115..4,895.600 rows=19,753,194 loops=1)

6. 1,221.424 3,330.220 ↑ 1.0 6,289,318 1

Hash (cost=272,113.44..272,113.44 rows=6,289,436 width=17) (actual time=3,330.220..3,330.220 rows=6,289,318 loops=1)

  • Buckets: 65,536 Batches: 128 Memory Usage: 2,942kB
7. 2,108.796 2,108.796 ↑ 1.0 6,289,318 1

Seq Scan on lh_spot_to_deal (cost=0.00..272,113.44 rows=6,289,436 width=17) (actual time=0.114..2,108.796 rows=6,289,318 loops=1)

  • Filter: (row_num = 1)
  • Rows Removed by Filter: 557
8. 32.380 60.447 ↑ 1.0 171,079 1

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

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,125kB
9. 28.067 28.067 ↑ 1.0 171,079 1

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

10.          

CTE stage_order_deals

11. 13,100.850 319,985.759 ↓ 1,173.2 234,643 1

HashAggregate (cost=493,693.90..493,695.90 rows=200 width=36) (actual time=319,892.725..319,985.759 rows=234,643 loops=1)

  • Group Key: os_1.order_id
12. 306,884.909 306,884.909 ↓ 1.0 19,753,194 1

CTE Scan on stage_spot_deals os_1 (cost=0.00..394,955.12 rows=19,747,756 width=222) (actual time=0.000..306,884.909 rows=19,753,194 loops=1)

13. 75.354 101.733 ↑ 1.0 19,898 1

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

  • Sort Key: dd.source_deal_id
  • Sort Method: quicksort Memory: 2,440kB
14. 1.646 26.379 ↑ 1.0 19,898 1

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

15. 11.299 24.733 ↑ 1.0 19,898 1

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

  • Group Key: dd_1.deal_sk
16. 3.669 13.434 ↑ 1.0 19,898 1

Hash Right Join (cost=1,529.70..1,543.62 rows=19,898 width=35) (actual time=9.908..13.434 rows=19,898 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.181 9.762 ↑ 1.0 19,898 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,562kB
19. 5.581 5.581 ↑ 1.0 19,898 1

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

20. 2,916.204 389,193.569 ↑ 1.8 11,034,151 1

Materialize (cost=11,020,646.11..11,119,384.89 rows=19,747,756 width=266) (actual time=382,578.759..389,193.569 rows=11,034,151 loops=1)

21. 50,823.081 386,277.365 ↑ 1.8 11,034,151 1

Sort (cost=11,020,646.11..11,070,015.50 rows=19,747,756 width=266) (actual time=382,578.744..386,277.365 rows=11,034,151 loops=1)

  • Sort Key: (((COALESCE(od.deal_id, (ds.deal_id)::text))::character varying(100))::text)
  • Sort Method: external merge Disk: 966,264kB
22. 9,731.936 335,454.284 ↓ 1.0 19,753,194 1

Hash Left Join (cost=6.50..1,135,502.47 rows=19,747,756 width=266) (actual time=323,496.815..335,454.284 rows=19,753,194 loops=1)

  • Hash Cond: (ds.order_id = od.order_id)
23. 5,617.567 5,617.567 ↓ 1.0 19,753,194 1

CTE Scan on stage_spot_deals ds (cost=0.00..394,955.12 rows=19,747,756 width=234) (actual time=3,392.024..5,617.567 rows=19,753,194 loops=1)

24. 47.051 320,104.781 ↓ 1,173.2 234,643 1

Hash (cost=4.00..4.00 rows=200 width=36) (actual time=320,104.781..320,104.781 rows=234,643 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3,687kB
25. 320,057.730 320,057.730 ↓ 1,173.2 234,643 1

CTE Scan on stage_order_deals od (cost=0.00..4.00 rows=200 width=36) (actual time=319,892.729..320,057.730 rows=234,643 loops=1)

Planning time : 0.931 ms
Execution time : 425,823.669 ms