explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L6Qd : Optimization for: Optimization for: plan #gJi3; plan #0xnv

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 117.337 ↑ 1.0 1 1

Limit (cost=1.55..16,535.85 rows=1 width=68) (actual time=117.337..117.337 rows=1 loops=1)

2. 0.025 117.336 ↑ 2.0 1 1

Nested Loop Left Join (cost=1.55..33,070.15 rows=2 width=68) (actual time=117.336..117.336 rows=1 loops=1)

  • Filter: (COALESCE(pt_pn.namesid_historic, pt_v.master_vehiclesid_historic) IS NOT NULL)
3. 0.002 117.310 ↑ 2.0 1 1

Nested Loop Left Join (cost=1.13..33,053.22 rows=2 width=68) (actual time=117.310..117.310 rows=1 loops=1)

4. 0.003 117.294 ↑ 2.0 1 1

Nested Loop Left Join (cost=0.71..33,050.89 rows=2 width=64) (actual time=117.294..117.294 rows=1 loops=1)

5. 2.661 117.281 ↑ 2.0 1 1

Nested Loop Left Join (cost=0.29..33,049.60 rows=2 width=64) (actual time=117.281..117.281 rows=1 loops=1)

  • Join Filter: (unit.dmv_unit_link = unit_v.dmv_unit_link)
  • Rows Removed by Join Filter: 20012
6. 11.861 39.149 ↑ 2.0 1 1

Nested Loop (cost=0.29..14,257.50 rows=2 width=56) (actual time=39.149..39.149 rows=1 loops=1)

  • Join Filter: (acc.dmv_location_link = unit.dmv_location_link)
  • Rows Removed by Join Filter: 56544
7. 0.011 1.957 ↑ 1.0 1 1

Nested Loop (cost=0.29..4,256.32 rows=1 width=28) (actual time=1.957..1.957 rows=1 loops=1)

8. 1.935 1.935 ↑ 1.0 1 1

Seq Scan on dmv_location_ni acc (cost=0.00..4,248.00 rows=1 width=12) (actual time=1.935..1.935 rows=1 loops=1)

9. 0.011 0.011 ↑ 1.0 1 1

Index Scan using stg_combined_accidents_convertedid_wtrun_idx on stg_combined_accidents pt_acc (cost=0.29..8.31 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: ((convertedid = acc.dmv_location_ni_zid) AND (wtrun = 390))
10. 25.331 25.331 ↑ 1.3 56,545 1

Seq Scan on dmv_unit_ni unit (cost=0.00..9,057.19 rows=75,519 width=36) (actual time=0.167..25.331 rows=56,545 loops=1)

11. 3.607 75.471 ↑ 3.6 20,013 1

Materialize (cost=0.00..16,786.80 rows=72,920 width=12) (actual time=0.428..75.471 rows=20,013 loops=1)

12. 71.864 71.864 ↑ 3.6 20,013 1

Seq Scan on dmv_vehicle_ni unit_v (cost=0.00..16,422.20 rows=72,920 width=12) (actual time=0.424..71.864 rows=20,013 loops=1)

13. 0.010 0.010 ↑ 1.0 1 1

Index Scan using ptvehicles_convertedid_wtrun_idx on pt_vehicles pt_v (cost=0.42..0.64 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((unit_v.dmv_vehicle_ni_zid = convertedid) AND (wtrun = 210))
14. 0.014 0.014 ↓ 0.0 0 1

Index Scan using dmv_name_ni_idx_ulink on dmv_name_ni driver (cost=0.42..1.16 rows=1 width=12) (actual time=0.014..0.014 rows=0 loops=1)

  • Index Cond: (unit.dmv_unit_link = dmv_unit_link)
  • Filter: (involvement_type = 'Driver'::text)
  • Rows Removed by Filter: 1
15. 0.001 0.001 ↓ 0.0 0 1

Index Scan using idx_pt_people_names_convertedid_wtrun on pt_people_names pt_pn (cost=0.42..8.45 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((driver.dmv_name_ni_zid = convertedid) AND (wtrun = 410))
Planning time : 2.836 ms
Execution time : 118.324 ms