explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SVGs : Optimization for: plan #IL4x

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 204.194 ↑ 1.0 1 1

Limit (cost=0.83..969.36 rows=1 width=78) (actual time=204.194..204.194 rows=1 loops=1)

2. 0.088 204.194 ↑ 161.0 1 1

Nested Loop (cost=0.83..155,933.92 rows=161 width=78) (actual time=204.194..204.194 rows=1 loops=1)

  • Join Filter: (co.offclass_guid = o.sqlguid)
  • Rows Removed by Join Filter: 408
3. 0.015 203.270 ↑ 35.0 1 1

Nested Loop Left Join (cost=0.83..154,321.24 rows=35 width=111) (actual time=203.270..203.270 rows=1 loops=1)

  • Join Filter: (co.racial = (bias.state_code)::text)
  • Rows Removed by Join Filter: 38
4. 0.006 137.692 ↑ 35.0 1 1

Nested Loop (cost=0.83..154,056.38 rows=35 width=106) (actual time=137.692..137.692 rows=1 loops=1)

5. 0.034 62.871 ↑ 35.0 1 1

Nested Loop (cost=0.42..154,034.72 rows=35 width=90) (actual time=62.871..62.871 rows=1 loops=1)

6. 62.434 62.434 ↑ 3,739.7 31 1

Seq Scan on incdmast i (cost=0.00..59,125.32 rows=115,932 width=52) (actual time=12.702..62.434 rows=31 loops=1)

7. 0.403 0.403 ↓ 0.0 0 31

Index Scan using dca_offense_incdno_idx on offense co (cost=0.42..0.81 rows=1 width=89) (actual time=0.013..0.013 rows=0 loops=31)

  • Index Cond: ((incdno = i.incdno) AND (deptno = i.dept) AND (yearof = i.yearof))
8. 74.815 74.815 ↑ 1.0 1 1

Index Scan using stg_combined_cases_wtrun_convertedid_idx on stg_combined_cases pt_c (cost=0.42..0.61 rows=1 width=32) (actual time=74.815..74.815 rows=1 loops=1)

  • Index Cond: ((wtrun = 101) AND (convertedid = i.incdmast_zid))
9. 0.010 65.563 ↓ 1.1 38 1

Materialize (cost=0.00..246.58 rows=35 width=15) (actual time=3.861..65.563 rows=38 loops=1)

10. 65.553 65.553 ↓ 1.1 38 1

Seq Scan on state_reporting_element_codes bias (cost=0.00..246.40 rows=35 width=15) (actual time=3.857..65.553 rows=38 loops=1)

  • Filter: (active AND ((element_tag)::text = 'bias_motivations'::text))
  • Rows Removed by Filter: 3,034
11. 0.089 0.836 ↑ 6.3 409 1

Materialize (cost=0.00..272.55 rows=2,561 width=36) (actual time=0.439..0.836 rows=409 loops=1)

12. 0.747 0.747 ↑ 6.3 409 1

Seq Scan on offclass o (cost=0.00..259.74 rows=2,561 width=36) (actual time=0.434..0.747 rows=409 loops=1)

  • Filter: (z_z_offense_offenses IS NOT NULL)
Planning time : 0.732 ms
Execution time : 204.292 ms