explain.depesz.com

PostgreSQL's explain analyze made readable

Result: faVS : Optimization for: Optimization for: Optimization for: plan #IL4x; plan #SVGs; plan #PK1t

Settings

Optimization path:

Optimization(s) for this plan:

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

Limit (cost=0.71..10,246.95 rows=1 width=44) (actual time=1,051.705..1,051.706 rows=1 loops=1)

2. 2.680 1,051.704 ↑ 13.0 1 1

Nested Loop (cost=0.71..133,201.83 rows=13 width=44) (actual time=1,051.704..1,051.704 rows=1 loops=1)

  • Join Filter: (co.offclass_guid = o.sqlguid)
  • Rows Removed by Join Filter: 29,360
3. 0.136 1,026.044 ↓ 20.0 20 1

Nested Loop (cost=0.71..132,910.05 rows=1 width=72) (actual time=327.986..1,026.044 rows=20 loops=1)

4. 397.381 896.728 ↓ 20.0 20 1

Nested Loop (cost=0.29..132,909.43 rows=1 width=56) (actual time=305.717..896.728 rows=20 loops=1)

  • Join Filter: ((a.deptno = i.dept) AND (a.yearof = i.yearof) AND (a.incdno = i.incdno))
  • Rows Removed by Join Filter: 2,068,203
5. 0.109 11.748 ↓ 7.0 21 1

Nested Loop (cost=0.29..63,481.36 rows=3 width=128) (actual time=9.475..11.748 rows=21 loops=1)

6. 10.926 10.926 ↑ 4,052.9 23 1

Seq Scan on charges co (cost=0.00..6,758.16 rows=93,216 width=92) (actual time=5.008..10.926 rows=23 loops=1)

7. 0.713 0.713 ↑ 1.0 1 23

Index Scan using dca_arrest_deptno_idx on arrest a (cost=0.29..0.60 rows=1 width=44) (actual time=0.028..0.031 rows=1 loops=23)

  • Index Cond: ((deptno = co.deptno) AND (yearof = co.yearof) AND (incdno = co.incdno))
  • Filter: (co.nameno = nameno)
  • Rows Removed by Filter: 0
8. 248.925 487.599 ↑ 1.2 98,487 21

Materialize (cost=0.00..60,723.98 rows=115,932 width=48) (actual time=0.972..23.219 rows=98,487 loops=21)

9. 238.674 238.674 ↑ 1.1 101,076 1

Seq Scan on incdmast i (cost=0.00..59,125.32 rows=115,932 width=48) (actual time=20.340..238.674 rows=101,076 loops=1)

10. 129.180 129.180 ↑ 1.0 1 20

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=6.458..6.459 rows=1 loops=20)

  • Index Cond: ((wtrun = 101) AND (convertedid = i.incdmast_zid))
11. 22.980 22.980 ↑ 1.7 1,468 20

Seq Scan on offclass o (cost=0.00..259.74 rows=2,561 width=36) (actual time=0.372..1.149 rows=1,468 loops=20)

  • Filter: (z_z_offense_offenses IS NOT NULL)
Planning time : 1.423 ms
Execution time : 1,763.666 ms