explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.003 1,955.810 ↑ 1.0 1 1

Limit (cost=27,765.54..32,606.41 rows=1 width=44) (actual time=1,955.809..1,955.810 rows=1 loops=1)

2. 0.224 1,955.807 ↑ 13.0 1 1

Nested Loop (cost=27,765.54..90,696.91 rows=13 width=44) (actual time=1,955.807..1,955.807 rows=1 loops=1)

  • Join Filter: (co.offclass_guid = o.sqlguid)
  • Rows Removed by Join Filter: 930
3. 0.009 1,954.049 ↑ 1.0 1 1

Nested Loop (cost=27,765.54..90,405.13 rows=1 width=72) (actual time=1,954.049..1,954.049 rows=1 loops=1)

4. 37.087 1,939.020 ↑ 1.0 1 1

Nested Loop (cost=27,765.12..90,404.51 rows=1 width=56) (actual time=1,939.020..1,939.020 rows=1 loops=1)

  • Join Filter: ((a.deptno = i.dept) AND (a.yearof = i.yearof) AND (a.incdno = i.incdno))
  • Rows Removed by Join Filter: 423,481
5. 0.061 1,060.833 ↓ 5.0 5 1

Merge Join (cost=27,765.12..29,250.38 rows=1 width=128) (actual time=1,060.768..1,060.833 rows=5 loops=1)

  • Merge Cond: ((co.deptno = a.deptno) AND (co.yearof = a.yearof) AND (co.incdno = a.incdno) AND (co.nameno = a.nameno))
6. 191.486 475.570 ↑ 15,536.0 6 1

Sort (cost=19,233.34..19,466.38 rows=93,216 width=92) (actual time=475.552..475.570 rows=6 loops=1)

  • Sort Key: co.deptno, co.yearof, co.incdno, co.nameno
  • Sort Method: external sort Disk: 3,688kB
7. 284.084 284.084 ↑ 1.9 49,233 1

Seq Scan on charges co (cost=0.00..6,758.16 rows=93,216 width=92) (actual time=60.783..284.084 rows=49,233 loops=1)

8. 97.594 585.202 ↑ 4,267.3 6 1

Sort (cost=8,531.78..8,595.79 rows=25,604 width=44) (actual time=585.197..585.202 rows=6 loops=1)

  • Sort Key: a.deptno, a.yearof, a.incdno, a.nameno
  • Sort Method: quicksort Memory: 4,026kB
9. 487.608 487.608 ↓ 2.1 53,113 1

Seq Scan on arrest a (cost=0.00..6,657.04 rows=25,604 width=44) (actual time=319.017..487.608 rows=53,113 loops=1)

10. 841.100 841.100 ↑ 1.4 84,696 5

Seq Scan on incdmast i (cost=0.00..59,125.32 rows=115,932 width=48) (actual time=14.637..168.220 rows=84,696 loops=5)

11. 15.020 15.020 ↑ 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=15.020..15.020 rows=1 loops=1)

  • Index Cond: ((wtrun = 101) AND (convertedid = i.incdmast_zid))
12. 1.534 1.534 ↑ 2.8 931 1

Seq Scan on offclass o (cost=0.00..259.74 rows=2,561 width=36) (actual time=0.656..1.534 rows=931 loops=1)

  • Filter: (z_z_offense_offenses IS NOT NULL)
Planning time : 72.817 ms
Execution time : 1,957.562 ms