explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gJi3

Settings

Optimization(s) for this plan:

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

Limit (cost=8,303.15..43,726.49 rows=1 width=68) (actual time=327.267..327.267 rows=1 loops=1)

2. 0.066 327.266 ↑ 1.0 1 1

Nested Loop Left Join (cost=8,303.15..43,726.49 rows=1 width=68) (actual time=327.266..327.266 rows=1 loops=1)

  • Filter: (COALESCE(pt_pn.namesid_historic, pt_v.master_vehiclesid_historic) IS NOT NULL)
3. 0.004 327.199 ↑ 1.0 1 1

Nested Loop Left Join (cost=8,302.73..43,718.03 rows=1 width=68) (actual time=327.199..327.199 rows=1 loops=1)

  • Join Filter: (unit.dmv_unit_link = driver.dmv_unit_link)
4. 0.001 216.292 ↑ 1.0 1 1

Nested Loop Left Join (cost=8,302.73..29,503.52 rows=1 width=64) (actual time=216.292..216.292 rows=1 loops=1)

5. 0.003 216.283 ↑ 1.0 1 1

Nested Loop (cost=8,302.30..29,502.91 rows=1 width=64) (actual time=216.283..216.283 rows=1 loops=1)

6. 0.292 216.268 ↑ 1.0 1 1

Nested Loop (cost=8,302.01..29,494.59 rows=1 width=48) (actual time=216.268..216.268 rows=1 loops=1)

  • Join Filter: (unit.dmv_location_link = acc.dmv_location_link)
  • Rows Removed by Join Filter: 2328
7. 0.056 210.659 ↑ 1.0 1 1

Hash Right Join (cost=8,302.01..25,246.58 rows=1 width=44) (actual time=210.659..210.659 rows=1 loops=1)

  • Hash Cond: (unit_v.dmv_unit_link = unit.dmv_unit_link)
8. 0.580 0.580 ↑ 45,511.0 2 1

Seq Scan on dmv_vehicle_ni unit_v (cost=0.00..16,603.22 rows=91,022 width=12) (actual time=0.369..0.580 rows=2 loops=1)

9. 18.074 210.023 ↓ 75,519.0 75,519 1

Hash (cost=8,302.00..8,302.00 rows=1 width=36) (actual time=210.023..210.023 rows=75,519 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 2 (originally 1) Memory Usage: 4312kB
10. 191.949 191.949 ↓ 75,519.0 75,519 1

Seq Scan on dmv_unit_ni unit (cost=0.00..8,302.00 rows=1 width=36) (actual time=0.212..191.949 rows=75,519 loops=1)

11. 5.317 5.317 ↓ 2,329.0 2,329 1

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

12. 0.012 0.012 ↑ 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.012..0.012 rows=1 loops=1)

  • Index Cond: ((convertedid = acc.dmv_location_ni_zid) AND (wtrun = 390))
13. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

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

Seq Scan on dmv_name_ni driver (cost=0.00..14,214.50 rows=1 width=12) (actual time=110.903..110.903 rows=0 loops=1)

  • Filter: (involvement_type = 'Driver'::text)
  • Rows Removed by Filter: 106200
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 : 8.434 ms
Execution time : 328.617 ms