explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

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

Limit (cost=0.71..9,935.99 rows=1 width=44) (actual time=826.280..826.280 rows=1 loops=1)

2. 2.064 826.279 ↑ 13.0 1 1

Nested Loop (cost=0.71..129,159.36 rows=13 width=44) (actual time=826.279..826.279 rows=1 loops=1)

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

Nested Loop (cost=0.71..128,867.58 rows=1 width=72) (actual time=289.931..806.375 rows=20 loops=1)

4. 338.072 806.020 ↓ 20.0 20 1

Nested Loop (cost=0.29..128,866.94 rows=1 width=56) (actual time=289.912..806.020 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.107 117.857 ↓ 7.0 21 1

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

6. 117.336 117.336 ↑ 4,052.9 23 1

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

7. 0.414 0.414 ↑ 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.016..0.018 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. 217.825 350.091 ↑ 1.0 98,487 21

Materialize (cost=0.00..59,501.08 rows=102,339 width=19) (actual time=0.394..16.671 rows=98,487 loops=21)

9. 132.266 132.266 ↑ 1.0 101,076 1

Seq Scan on incdmast i (cost=0.00..58,989.39 rows=102,339 width=19) (actual time=8.182..132.266 rows=101,076 loops=1)

10. 0.260 0.260 ↑ 1.0 1 20

Index Scan using stg_combined_cases_wtrun_convertedid_idx on stg_combined_cases pt_c (cost=0.42..0.63 rows=1 width=32) (actual time=0.012..0.013 rows=1 loops=20)

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

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

  • Filter: (z_z_offense_offenses IS NOT NULL)
Planning time : 0.872 ms
Execution time : 827.051 ms