explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0xnv : Optimization for: plan #gJi3

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 129.717 ↑ 1.0 1 1

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

2. 0.028 129.715 ↑ 2.0 1 1

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

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

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

4. 0.002 129.664 ↑ 2.0 1 1

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

5. 2.598 129.646 ↑ 2.0 1 1

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

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

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

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

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

8. 1.536 1.536 ↑ 1.0 1 1

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

9. 0.040 0.040 ↑ 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.040..0.040 rows=1 loops=1)

  • Index Cond: ((convertedid = acc.dmv_location_ni_zid) AND (wtrun = 390))
10. 26.017 26.017 ↑ 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.155..26.017 rows=56,545 loops=1)

11. 3.943 86.345 ↑ 4.5 20,013 1

Materialize (cost=0.00..17,058.33 rows=91,022 width=12) (actual time=0.475..86.345 rows=20,013 loops=1)

12. 82.402 82.402 ↑ 4.5 20,013 1

Seq Scan on dmv_vehicle_ni unit_v (cost=0.00..16,603.22 rows=91,022 width=12) (actual time=0.471..82.402 rows=20,013 loops=1)

13. 0.016 0.016 ↑ 1.0 1 1

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

  • Index Cond: ((unit_v.dmv_vehicle_ni_zid = convertedid) AND (wtrun = 210))
14. 0.020 0.020 ↓ 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.020..0.020 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.000 0.000 ↓ 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.000..0.000 rows=0 loops=1)

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