explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IHS

Settings
# exclusive inclusive rows x rows loops node
1. 2,590.867 158,720.278 ↓ 0.0 0 1

Insert on pg_temp_23.refobjdsc (cost=114,552.55..120,458.01 rows=7,565 width=266) (actual time=158,720.278..158,720.278 rows=0 loops=1)

  • Buffers: shared hit=8481729 read=19078, local hit=8587471 read=3700145 dirtied=33512 written=33418
2. 1,233.522 156,129.411 ↓ 108.2 818,355 1

Nested Loop (cost=114,552.55..120,458.01 rows=7,565 width=266) (actual time=7,759.693..156,129.411 rows=818,355 loops=1)

  • Output: od.idobj, rk.idnam, od.inftyp, od.infsubtyp, od.blkno, od.ordnum, od.prop, od.infval
  • Buffers: shared hit=8481729 read=19078, local hit=7681129 read=3666632 written=33093
3. 1,608.643 11,916.369 ↓ 36,623.9 2,234,055 1

Hash Join (cost=114,551.58..119,925.07 rows=61 width=229) (actual time=7,565.914..11,916.369 rows=2,234,055 loops=1)

  • Output: pi.inftyp, pi.infsubtyp, icp.idnam, icp.infval
  • Hash Cond: (icp.idprop = pi.idprop)
  • Buffers: local hit=3 read=74923
4. 6,813.847 10,307.265 ↓ 10.0 2,267,184 1

HashAggregate (cost=114,512.17..116,774.69 rows=226,252 width=225) (actual time=7,565.429..10,307.265 rows=2,267,184 loops=1)

  • Output: icp.idnam, icp.idprop, icp.infval
  • Group Key: (icp.idnam)::text, icp.idprop, (icp.infval)::text
  • Buffers: local hit=1 read=74917
5. 3,493.418 3,493.418 ↓ 1.0 2,273,234 1

Seq Scan on pg_temp_23.incharproperties icp (cost=0.00..97,543.24 rows=2,262,524 width=225) (actual time=0.020..3,493.418 rows=2,273,234 loops=1)

  • Output: icp.idnam, icp.idprop, icp.infval, icp.idnam, icp.infval
  • Buffers: local hit=1 read=74917
6. 0.200 0.461 ↑ 1.0 1,396 1

Hash (cost=21.96..21.96 rows=1,396 width=12) (actual time=0.461..0.461 rows=1,396 loops=1)

  • Output: pi.inftyp, pi.infsubtyp, pi.idprop
  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
  • Buffers: local hit=2 read=6
7. 0.261 0.261 ↑ 1.0 1,396 1

Seq Scan on pg_temp_23.propertyids pi (cost=0.00..21.96 rows=1,396 width=12) (actual time=0.023..0.261 rows=1,396 loops=1)

  • Output: pi.inftyp, pi.infsubtyp, pi.idprop
  • Buffers: local hit=2 read=6
8. 4,474.125 142,979.520 ↓ 0.0 0 2,234,055

Nested Loop (cost=0.97..8.73 rows=1 width=266) (actual time=0.062..0.064 rows=0 loops=2,234,055)

  • Output: od.idobj, od.inftyp, od.infsubtyp, od.blkno, od.ordnum, od.prop, od.infval, rk.idnam
  • Join Filter: ((icp.infval)::text = (od.infval)::text)
  • Rows Removed by Join Filter: 5
  • Buffers: shared hit=8481729 read=19078, local hit=7681126 read=3591709 written=33093
9. 105,000.585 105,000.585 ↑ 1.0 1 2,234,055

Index Scan using idx_refkeys_idnam on pg_temp_23.refkeys rk (cost=0.55..0.95 rows=1 width=151) (actual time=0.046..0.047 rows=1 loops=2,234,055)

  • Output: rk.idkey, rk.objtyp, rk.idnam
  • Index Cond: ((rk.idnam)::text = (icp.idnam)::text)
  • Buffers: local hit=7681126 read=3591709 written=33093
10. 33,504.810 33,504.810 ↓ 6.0 6 2,233,654

Index Scan using pk_objdsc on ref_castoncast_local_v0.objdsc od (cost=0.42..7.76 rows=1 width=119) (actual time=0.013..0.015 rows=6 loops=2,233,654)

  • Output: od.idobj, od.inftyp, od.infsubtyp, od.blkno, od.ordnum, od.prop, od.infval
  • Index Cond: ((od.idobj = rk.idkey) AND (od.inftyp = pi.inftyp) AND (od.infsubtyp = pi.infsubtyp))
  • Buffers: shared hit=8481729 read=19078