explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1jSM

Settings
# exclusive inclusive rows x rows loops node
1. 0.036 510.199 ↓ 14.3 100 1

Limit (cost=17,898.80..561,147.93 rows=7 width=628) (actual time=215.682..510.199 rows=100 loops=1)

2. 17.509 510.163 ↓ 14.3 100 1

Nested Loop Left Join (cost=17,898.80..561,147.93 rows=7 width=628) (actual time=215.680..510.163 rows=100 loops=1)

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

Nested Loop Left Join (cost=17,898.80..560,808.56 rows=7 width=403) (actual time=214.327..482.454 rows=100 loops=1)

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

Nested Loop Left Join (cost=17,898.80..560,470.50 rows=7 width=387) (actual time=213.351..456.413 rows=100 loops=1)

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

Nested Loop Left Join (cost=17,898.80..560,431.13 rows=7 width=383) (actual time=213.345..456.344 rows=100 loops=1)

6. 0.151 456.073 ↓ 14.3 100 1

Nested Loop Left Join (cost=17,898.37..560,427.18 rows=7 width=356) (actual time=213.332..456.073 rows=100 loops=1)

7. 150.693 455.522 ↓ 14.3 100 1

Nested Loop Left Join (cost=17,897.94..560,423.18 rows=7 width=352) (actual time=213.308..455.522 rows=100 loops=1)

  • Join Filter: ((vs.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.189 187.029 ↓ 14.3 100 1

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

9. 2.456 186.440 ↓ 14.3 100 1

Hash Join (cost=15,552.07..554,832.49 rows=7 width=180) (actual time=186.027..186.440 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.015 2.015 ↑ 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.131..2.015 rows=6,569 loops=1)

11. 101.262 181.969 ↑ 1.0 175,239 1

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

  • Buckets: 32,768 Batches: 16 Memory Usage: 2,361kB
12. 80.707 80.707 ↑ 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.126..80.707 rows=175,239 loops=1)

13. 0.400 0.400 ↑ 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.004..0.004 rows=1 loops=100)

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

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

15. 6.038 30.879 ↑ 1.0 19,897 1

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

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

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

  • Hash Cond: ((d.deal_cd)::text = (dd.source_deal_id)::text)
17. 1.812 1.812 ↑ 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.812 rows=19,899 loops=1)

18. 3.226 6.805 ↑ 1.0 19,898 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,112kB
19. 3.579 3.579 ↑ 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.110..3.579 rows=19,898 loops=1)

20. 3.478 6.901 ↑ 1.0 19,899 1

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

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

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

22. 0.400 0.400 ↑ 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.004..0.004 rows=1 loops=100)

  • Index Cond: (vs.booked_spot_id = booked_spot_id)
23. 0.200 0.200 ↓ 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.002..0.002 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.415 10.900 ↑ 1.0 2,409 100

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

27. 0.485 0.485 ↑ 1.0 2,409 1

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

28. 9.678 10.200 ↑ 1.0 2,193 100

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

29. 0.522 0.522 ↑ 1.0 2,193 1

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

Planning time : 4.797 ms
Execution time : 530.399 ms