explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A60N

Settings
# exclusive inclusive rows x rows loops node
1. 6,328.702 230,723.435 ↓ 0.0 0 1

Insert on pg_temp_23.refobjinf (cost=121,033.82..130,909.46 rows=104,921 width=167) (actual time=230,723.435..230,723.435 rows=0 loops=1)

  • Buffers: shared hit=11442102 read=29372, local hit=12705501 read=4818935 dirtied=69170 written=69036
2. 1,677.729 224,394.733 ↓ 26.2 2,751,804 1

Nested Loop (cost=121,033.82..129,860.25 rows=104,921 width=167) (actual time=5,561.933..224,394.733 rows=2,751,804 loops=1)

  • Output: oi.idobj, rk.idnam, oi.inftyp, oi.infsubtyp, oi.blkno, oi.infval
  • Buffers: shared hit=11442102 read=29372, local hit=9763452 read=4749764 written=67975
3.          

CTE objectproperties

4. 0.039 22.873 ↑ 8.9 14 1

Nested Loop (cost=93.16..386.23 rows=124 width=8) (actual time=1.348..22.873 rows=14 loops=1)

  • Output: pa1.intval, pa2.intval
  • Buffers: shared hit=41 read=113
5. 0.222 20.146 ↑ 14.4 14 1

Hash Join (cost=92.88..264.67 rows=201 width=12) (actual time=1.303..20.146 rows=14 loops=1)

  • Output: p.idprop, pa1.intval, pa1.idprop
  • Hash Cond: (pa1.idprop = p.idprop)
  • Buffers: shared hit=4 read=107
6. 18.735 18.735 ↑ 1.0 1,396 1

Seq Scan on ref_castoncast_local_v0.propattr pa1 (cost=0.00..164.55 rows=1,396 width=8) (actual time=0.076..18.735 rows=1,396 loops=1)

  • Output: pa1.idprop, pa1.attrnam, pa1.attrtyp, pa1.intval, pa1.strval, pa1.status
  • Filter: ((pa1.attrnam)::text = 'INF_TYPE'::text)
  • Rows Removed by Filter: 6488
  • Buffers: shared hit=2 read=64
7. 0.094 1.189 ↑ 1.0 483 1

Hash (cost=86.84..86.84 rows=483 width=4) (actual time=1.189..1.189 rows=483 loops=1)

  • Output: p.idprop
  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
  • Buffers: shared hit=2 read=43
8. 1.095 1.095 ↑ 1.0 483 1

Seq Scan on ref_castoncast_local_v0.prop p (cost=0.00..86.84 rows=483 width=4) (actual time=0.037..1.095 rows=483 loops=1)

  • Output: p.idprop
  • Filter: (p.proptyp = 1028)
  • Rows Removed by Filter: 2864
  • Buffers: shared hit=2 read=43
9. 2.688 2.688 ↑ 1.0 1 14

Index Scan using idx_propattr on ref_castoncast_local_v0.propattr pa2 (cost=0.28..0.59 rows=1 width=8) (actual time=0.166..0.192 rows=1 loops=14)

  • Output: pa2.idprop, pa2.attrnam, pa2.attrtyp, pa2.intval, pa2.strval, pa2.status
  • Index Cond: ((pa2.idprop = pa1.idprop) AND ((pa2.attrnam)::text = 'INF_SUB_TYPE'::text))
  • Buffers: shared hit=37 read=6
10. 1,957.074 10,933.370 ↓ 17,240.6 2,861,941 1

Hash Join (cost=120,643.83..127,517.93 rows=166 width=165) (actual time=5,497.187..10,933.370 rows=2,861,941 loops=1)

  • Output: pi.inftyp, pi.infsubtyp, iip.idnam, iip.infval
  • Hash Cond: (iip.idprop = pi.idprop)
  • Buffers: local hit=8 read=70195 written=8
11. 7,479.159 8,975.855 ↓ 9.9 2,876,894 1

HashAggregate (cost=120,604.42..123,498.78 rows=289,436 width=161) (actual time=5,496.729..8,975.855 rows=2,876,894 loops=1)

  • Output: iip.idnam, iip.idprop, iip.infval
  • Group Key: (iip.idnam)::text, iip.idprop, iip.infval
  • Buffers: local read=70195 written=8
12. 1,496.696 1,496.696 ↑ 1.0 2,879,923 1

Seq Scan on pg_temp_23.inintproperties iip (cost=0.00..99,000.38 rows=2,880,538 width=161) (actual time=0.037..1,496.696 rows=2,879,923 loops=1)

  • Output: iip.idnam, iip.idprop, iip.infval, iip.idnam
  • Buffers: local read=70195 written=8
13. 0.221 0.441 ↑ 1.0 1,396 1

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

  • Output: pi.inftyp, pi.infsubtyp, pi.idprop
  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
  • Buffers: local hit=8
14. 0.220 0.220 ↑ 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.026..0.220 rows=1,396 loops=1)

  • Output: pi.inftyp, pi.infsubtyp, pi.idprop
  • Buffers: local hit=8
15. 5,729.069 211,783.634 ↑ 1.0 1 2,861,941

Nested Loop (cost=3.77..11.77 rows=1 width=167) (actual time=0.073..0.074 rows=1 loops=2,861,941)

  • Output: oi.idobj, oi.inftyp, oi.infsubtyp, oi.blkno, oi.infval, rk.idnam
  • Join Filter: (iip.infval = oi.infval)
  • Rows Removed by Join Filter: 0
  • Buffers: shared hit=11442102 read=29372, local hit=9763444 read=4679569 written=67967
16. 145,958.991 145,958.991 ↑ 1.0 1 2,861,941

Index Scan using idx_refkeys_idnam on pg_temp_23.refkeys rk (cost=0.55..0.87 rows=1 width=151) (actual time=0.051..0.051 rows=1 loops=2,861,941)

  • Output: rk.idkey, rk.objtyp, rk.idnam
  • Index Cond: ((rk.idnam)::text = (iip.idnam)::text)
  • Buffers: local hit=9763444 read=4679569 written=67967
17. 60,072.685 60,095.574 ↑ 1.0 1 2,861,694

Index Scan using pk_objinf on ref_castoncast_local_v0.objinf oi (cost=3.22..10.89 rows=1 width=20) (actual time=0.021..0.021 rows=1 loops=2,861,694)

  • Output: oi.idobj, oi.inftyp, oi.infsubtyp, oi.blkno, oi.infval
  • Index Cond: ((oi.idobj = rk.idkey) AND (oi.inftyp = pi.inftyp) AND (oi.infsubtyp = pi.infsubtyp))
  • Filter: ((NOT (hashed SubPlan 2)) AND ((oi.inftyp <> 9) OR ((oi.infsubtyp >= 2000) AND (oi.infsubtyp <= 2000000))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=11442102 read=29372
18.          

SubPlan (forIndex Scan)

19. 22.889 22.889 ↑ 8.9 14 1

CTE Scan on objectproperties op (cost=0.00..2.48 rows=124 width=8) (actual time=1.352..22.889 rows=14 loops=1)

  • Output: op.inftyp, op.infsubtyp
  • Buffers: shared hit=41 read=113