explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dwdv

Settings
# exclusive inclusive rows x rows loops node
1. 2,703.505 256,927.662 ↑ 88.1 8,354,625 1

Merge Join (cost=8,249,024.63..24,823,865.27 rows=735,836,980 width=259) (actual time=249,987.043..256,927.662 rows=8,354,625 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. 180,585.475 206,479.536 ↓ 1.2 8,448,728 1

Hash Left Join (cost=399,766.52..4,016,249.43 rows=7,343,316 width=234) (actual time=8,050.440..206,479.536 rows=8,448,728 loops=1)

  • Hash Cond: (os.order_id = lh_order_to_deal.order_id)
4. 5,356.537 25,560.755 ↓ 1.2 8,448,728 1

Hash Left Join (cost=392,058.55..1,956,160.87 rows=7,343,316 width=34) (actual time=7,715.908..25,560.755 rows=8,448,728 loops=1)

  • Hash Cond: (os.source_spot_id = lh_spot_to_deal.usn)
5. 12,488.765 12,488.765 ↓ 1.2 8,448,728 1

Seq Scan on blended_spots os (cost=0.00..1,343,818.36 rows=7,343,316 width=21) (actual time=0.162..12,488.765 rows=8,448,728 loops=1)

  • Filter: ((source_spot_id_desc = ANY ('{"usn - aired spot","usn - booked spot"}'::text[])) OR ((source_spot_id_desc = 'spot_id - admiral'::text) AND (additional_admiral_spot = 1)) OR (is_manual_adjustment = 1))
  • Rows Removed by Filter: 11,481,179
6. 1,411.726 7,715.453 ↑ 1.0 6,367,890 1

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

  • Buckets: 65,536 Batches: 128 Memory Usage: 2,970kB
7. 6,303.727 6,303.727 ↑ 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.526..6,303.727 rows=6,367,890 loops=1)

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

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

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,136kB
9. 295.808 295.808 ↑ 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=4.788..295.808 rows=172,043 loops=1)

10.          

CTE stage_order_deals

11. 5,956.437 209,504.611 ↓ 866.1 173,226 1

HashAggregate (cost=183,582.90..183,584.90 rows=200 width=36) (actual time=209,437.910..209,504.611 rows=173,226 loops=1)

  • Group Key: os_1.order_id
12. 203,548.174 203,548.174 ↓ 1.2 8,448,728 1

CTE Scan on stage_spot_deals os_1 (cost=0.00..146,866.32 rows=7,343,316 width=222) (actual time=0.000..203,548.174 rows=8,448,728 loops=1)

13. 73.982 99.768 ↑ 1.0 20,041 1

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

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

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

15. 8.974 24.098 ↑ 1.0 20,041 1

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

  • Group Key: dd_1.deal_sk
16. 2.559 15.124 ↑ 1.0 20,041 1

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

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

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

18. 4.063 12.564 ↑ 1.0 20,041 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,570kB
19. 8.501 8.501 ↑ 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.144..8.501 rows=20,041 loops=1)

20. 1,565.848 254,124.389 ↓ 1.1 8,354,668 1

Materialize (cost=4,045,696.44..4,082,413.02 rows=7,343,316 width=266) (actual time=249,892.901..254,124.389 rows=8,354,668 loops=1)

21. 30,283.728 252,558.541 ↓ 1.1 8,354,668 1

Sort (cost=4,045,696.44..4,064,054.73 rows=7,343,316 width=266) (actual time=249,892.885..252,558.541 rows=8,354,668 loops=1)

  • Sort Key: (((COALESCE(od.deal_id, (ds.deal_id)::text))::character varying(100))::text)
  • Sort Method: external merge Disk: 572,328kB
22. 3,689.158 222,274.813 ↓ 1.2 8,448,728 1

Hash Left Join (cost=6.50..422,247.17 rows=7,343,316 width=266) (actual time=217,645.740..222,274.813 rows=8,448,728 loops=1)

  • Hash Cond: (ds.order_id = od.order_id)
23. 8,990.366 8,990.366 ↓ 1.2 8,448,728 1

CTE Scan on stage_spot_deals ds (cost=0.00..146,866.32 rows=7,343,316 width=234) (actual time=8,050.442..8,990.366 rows=8,448,728 loops=1)

24. 36.639 209,595.289 ↓ 866.1 173,226 1

Hash (cost=4.00..4.00 rows=200 width=36) (actual time=209,595.289..209,595.289 rows=173,226 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3,174kB
25. 209,558.650 209,558.650 ↓ 866.1 173,226 1

CTE Scan on stage_order_deals od (cost=0.00..4.00 rows=200 width=36) (actual time=209,437.912..209,558.650 rows=173,226 loops=1)

Planning time : 43.489 ms
Execution time : 280,480.338 ms