explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IL4x

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 2,043.555 ↑ 1.0 1 1

Limit (cost=13,648.41..13,891.29 rows=1 width=108) (actual time=2,043.555..2,043.555 rows=1 loops=1)

2. 0.104 2,043.553 ↑ 11,589.0 1 1

Nested Loop (cost=13,648.41..2,828,448.84 rows=11,589 width=108) (actual time=2,043.553..2,043.553 rows=1 loops=1)

  • Join Filter: (co.offclass_guid = o.sqlguid)
  • Rows Removed by Join Filter: 186
3. 0.016 2,042.518 ↑ 905.0 1 1

Nested Loop Left Join (cost=13,648.41..2,793,272.26 rows=905 width=136) (actual time=2,042.518..2,042.518 rows=1 loops=1)

  • Join Filter: (co.racial = (bias.state_code)::text)
  • Rows Removed by Join Filter: 38
4. 0.008 2,014.216 ↑ 905.0 1 1

Nested Loop (cost=13,648.41..2,792,550.65 rows=905 width=160) (actual time=2,014.216..2,014.216 rows=1 loops=1)

5. 19.477 1,989.299 ↑ 905.0 1 1

Hash Join (cost=13,647.99..2,791,990.63 rows=905 width=144) (actual time=1,989.299..1,989.299 rows=1 loops=1)

  • Hash Cond: ((i.dept = co.deptno) AND (i.yearof = co.yearof) AND (i.incdno = co.incdno))
6. 125.737 125.737 ↑ 3,220.3 36 1

Seq Scan on incdmast i (cost=0.00..59,125.32 rows=115,932 width=52) (actual time=32.154..125.737 rows=36 loops=1)

7. 42.222 1,844.085 ↓ 1.1 71,650 1

Hash (cost=11,030.36..11,030.36 rows=62,436 width=172) (actual time=1,844.085..1,844.085 rows=71,650 loops=1)

  • Buckets: 32,768 Batches: 4 Memory Usage: 2,387kB
8. 1,801.863 1,801.863 ↓ 1.1 71,652 1

Seq Scan on offense co (cost=0.00..11,030.36 rows=62,436 width=172) (actual time=860.091..1,801.863 rows=71,652 loops=1)

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

  • Index Cond: ((wtrun = 101) AND (convertedid = i.incdmast_zid))
10. 0.017 28.286 ↓ 1.1 38 1

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

11. 28.269 28.269 ↓ 1.1 38 1

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

  • Filter: (active AND ((element_tag)::text = 'bias_motivations'::text))
  • Rows Removed by Filter: 3,034
12. 0.051 0.931 ↑ 13.7 187 1

Materialize (cost=0.00..272.55 rows=2,561 width=36) (actual time=0.593..0.931 rows=187 loops=1)

13. 0.880 0.880 ↑ 13.7 187 1

Seq Scan on offclass o (cost=0.00..259.74 rows=2,561 width=36) (actual time=0.586..0.880 rows=187 loops=1)

  • Filter: (z_z_offense_offenses IS NOT NULL)
Planning time : 91.469 ms
Execution time : 2,045.788 ms