explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7N1G

Settings
# exclusive inclusive rows x rows loops node
1. 0.042 517.943 ↓ 14.3 100 1

Limit (cost=17,898.80..561,147.93 rows=7 width=640) (actual time=209.050..517.943 rows=100 loops=1)

2. 19.764 517.901 ↓ 14.3 100 1

Nested Loop Left Join (cost=17,898.80..561,147.93 rows=7 width=640) (actual time=209.048..517.901 rows=100 loops=1)

  • Join Filter: (s.spot_id = ac.spot_id)
  • Rows Removed by Join Filter: 219,300
3. 15.355 488.237 ↓ 14.3 100 1

Nested Loop Left Join (cost=17,898.80..560,808.56 rows=7 width=415) (actual time=207.651..488.237 rows=100 loops=1)

  • Join Filter: (s.spot_id = la.spot_id)
  • Rows Removed by Join Filter: 240,900
4. 0.149 461.582 ↓ 14.3 100 1

Nested Loop Left Join (cost=17,898.80..560,470.50 rows=7 width=399) (actual time=206.649..461.582 rows=100 loops=1)

  • Join Filter: (s.bias_number = cl.bias_number)
5. 0.230 461.433 ↓ 14.3 100 1

Nested Loop Left Join (cost=17,898.80..560,431.13 rows=7 width=395) (actual time=206.645..461.433 rows=100 loops=1)

6. 0.179 460.903 ↓ 14.3 100 1

Nested Loop Left Join (cost=17,898.37..560,427.18 rows=7 width=368) (actual time=206.632..460.903 rows=100 loops=1)

7. 158.880 460.024 ↓ 14.3 100 1

Nested Loop Left Join (cost=17,897.94..560,423.18 rows=7 width=364) (actual time=206.605..460.024 rows=100 loops=1)

  • Join Filter: ((COALESCE(vs.deal_cd, sb.deal_cd))::text = (d.deal_cd)::text)
  • Rows Removed by Join Filter: 1,981,678
  • Filter: (((COALESCE(vs.deal_cd, sb.deal_cd) IS NOT NULL) AND (d.deal_cd IS NOT NULL)) OR (COALESCE(vs.deal_cd, sb.deal_cd) IS NULL))
8. 0.269 179.144 ↓ 14.3 100 1

Nested Loop (cost=15,552.51..554,836.88 rows=7 width=353) (actual time=177.585..179.144 rows=100 loops=1)

9. 2.576 178.175 ↓ 14.3 100 1

Hash Join (cost=15,552.07..554,832.49 rows=7 width=180) (actual time=177.558..178.175 rows=100 loops=1)

  • Hash Cond: (((s.bias_entry_date)::text = (sb.bias_entry_date)::text) AND (s.bias_number = sb.bias_number) AND ((s.network_cd)::text = (sb.network_cd)::text))
10. 2.124 2.124 ↑ 1,129.9 6,569 1

Seq Scan on spot s (cost=0.00..306,880.87 rows=7,422,087 width=50) (actual time=0.121..2.124 rows=6,569 loops=1)

11. 96.283 173.475 ↑ 1.0 175,239 1

Hash (cost=8,548.39..8,548.39 rows=175,239 width=158) (actual time=173.474..173.475 rows=175,239 loops=1)

  • Buckets: 32,768 Batches: 16 Memory Usage: 2,359kB
12. 77.192 77.192 ↑ 1.0 175,239 1

Seq Scan on booked_order sb (cost=0.00..8,548.39 rows=175,239 width=158) (actual time=0.162..77.192 rows=175,239 loops=1)

13. 0.700 0.700 ↑ 1.0 1 100

Index Scan using idx_lh_vwspot_spotid on vw_spot vs (cost=0.43..0.62 rows=1 width=173) (actual time=0.006..0.007 rows=1 loops=100)

  • Index Cond: (spot_id = s.spot_id)
14. 87.921 122.000 ↑ 1.0 19,818 100

Materialize (cost=2,345.43..3,546.76 rows=19,898 width=23) (actual time=0.144..1.220 rows=19,818 loops=100)

15. 7.133 34.079 ↑ 1.0 19,897 1

Hash Join (cost=2,345.43..3,447.27 rows=19,898 width=23) (actual time=14.398..34.079 rows=19,897 loops=1)

  • Hash Cond: ((d.deal_cd)::text = (ldp.deal_cd)::text)
16. 11.150 19.873 ↑ 1.0 19,897 1

Hash Join (cost=1,529.70..2,357.94 rows=19,898 width=33) (actual time=7.176..19.873 rows=19,897 loops=1)

  • Hash Cond: ((d.deal_cd)::text = (dd.source_deal_id)::text)
17. 1.830 1.830 ↑ 1.0 19,899 1

Seq Scan on deal d (cost=0.00..775.99 rows=19,899 width=21) (actual time=0.125..1.830 rows=19,899 loops=1)

18. 3.273 6.893 ↑ 1.0 19,898 1

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

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

Seq Scan on dim_deal dd (cost=0.00..1,280.98 rows=19,898 width=12) (actual time=0.115..3.620 rows=19,898 loops=1)

20. 3.601 7.073 ↑ 1.0 19,899 1

Hash (cost=566.99..566.99 rows=19,899 width=14) (actual time=7.073..7.073 rows=19,899 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,151kB
21. 3.472 3.472 ↑ 1.0 19,899 1

Seq Scan on deal_properties ldp (cost=0.00..566.99 rows=19,899 width=14) (actual time=0.111..3.472 rows=19,899 loops=1)

22. 0.700 0.700 ↑ 1.0 1 100

Index Scan using idx_lh_bs_bookedspotid on booked_spot bs (cost=0.43..0.56 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=100)

  • Index Cond: (vs.booked_spot_id = booked_spot_id)
23. 0.300 0.300 ↓ 0.0 0 100

Index Scan using idx_lh_air_airedspotid on aired_spot air (cost=0.43..0.55 rows=1 width=35) (actual time=0.003..0.003 rows=0 loops=100)

  • Index Cond: (vs.aired_spot_id = aired_spot_id)
24. 0.000 0.000 ↓ 0.0 0 100

Materialize (cost=0.00..13.75 rows=250 width=8) (actual time=0.000..0.000 rows=0 loops=100)

25. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on change_log_entries cl (cost=0.00..12.50 rows=250 width=8) (actual time=0.002..0.002 rows=0 loops=1)

26. 10.807 11.300 ↑ 1.0 2,409 100

Materialize (cost=0.00..91.14 rows=2,409 width=16) (actual time=0.000..0.113 rows=2,409 loops=100)

27. 0.493 0.493 ↑ 1.0 2,409 1

Seq Scan on lighthouse_adjustment la (cost=0.00..79.09 rows=2,409 width=16) (actual time=0.006..0.493 rows=2,409 loops=1)

28. 9.374 9.900 ↑ 1.0 2,193 100

Materialize (cost=0.00..111.90 rows=2,193 width=16) (actual time=0.000..0.099 rows=2,193 loops=100)

29. 0.526 0.526 ↑ 1.0 2,193 1

Seq Scan on adjustment_comment ac (cost=0.00..100.93 rows=2,193 width=16) (actual time=0.004..0.526 rows=2,193 loops=1)

Planning time : 3.999 ms
Execution time : 539.300 ms