explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MfS3

Settings
# exclusive inclusive rows x rows loops node
1. 2.129 58,997.968 ↓ 0.0 0 1

Insert on pg_temp_6.kb_deleteobjfilref (cost=15,502,944.49..15,507,227.67 rows=214,159 width=12) (actual time=58,997.968..58,997.968 rows=0 loops=1)

  • Buffers: shared hit=3542222 read=2119, local hit=1396 read=1696 dirtied=1 written=440
2. 0.654 58,995.839 ↑ 2,611.7 82 1

HashAggregate (cost=15,502,944.49..15,505,086.08 rows=214,159 width=12) (actual time=58,995.320..58,995.839 rows=82 loops=1)

  • Output: ofr.idobj, ofr.idfilref, ofr.idfil
  • Group Key: ofr.idobj, ofr.idfilref, ofr.idfil
  • Buffers: shared hit=3542222 read=2119, local hit=1315 read=1695 written=440
3. 6.301 58,995.185 ↑ 31,640.3 82 1

Hash Join (cost=15,374,345.18..15,483,485.73 rows=2,594,501 width=12) (actual time=58,862.303..58,995.185 rows=82 loops=1)

  • Output: ofr.idobj, ofr.idfilref, ofr.idfil
  • Hash Cond: (ofr.idfil = op.idobj)
  • Buffers: shared hit=3542222 read=2119, local hit=1315 read=1695 written=440
4. 141.336 58,528.121 ↑ 2,257.0 82 1

Merge Anti Join (cost=15,322,573.72..15,328,269.07 rows=185,075 width=12) (actual time=58,395.325..58,528.121 rows=82 loops=1)

  • Output: ofr.idobj, ofr.idfilref, ofr.idfil
  • Merge Cond: ((ofr.idobj = tofr.idobj) AND (ofr.idfilref = tofr.idfilref) AND (ofr.idfil = tofr.idfil))
  • Buffers: shared hit=3533922 read=2119, local hit=1315 read=1691 written=440
5. 277.321 58,193.112 ↓ 1.5 321,952 1

Sort (cost=15,284,211.74..15,284,740.53 rows=211,514 width=12) (actual time=58,125.454..58,193.112 rows=321,952 loops=1)

  • Output: ofr.idobj, ofr.idfilref, ofr.idfil
  • Sort Key: ofr.idobj, ofr.idfilref, ofr.idfil
  • Sort Method: quicksort Memory: 27380kB
  • Buffers: shared hit=3533922 read=2119, local read=1251 written=149
6. 185.061 57,915.791 ↓ 1.5 321,952 1

Nested Loop (cost=0.42..15,265,502.91 rows=211,514 width=12) (actual time=29.276..57,915.791 rows=321,952 loops=1)

  • Output: ofr.idobj, ofr.idfilref, ofr.idfil
  • Buffers: shared hit=3533922 read=2119, local read=1251 written=149
7. 102.730 102.730 ↑ 1.0 230,512 1

Seq Scan on pg_temp_6.kb_work_fullscopeobjects kwfsoc (cost=0.00..3,552.12 rows=230,512 width=4) (actual time=0.734..102.730 rows=230,512 loops=1)

  • Output: kwfsoc.objectid, kwfsoc.objectkind, kwfsoc.idobj
  • Buffers: local read=1247 written=149
8. 1,930.304 57,628.000 ↑ 1.0 1 230,512

Index Scan using objfilref_idobj on diw_castoncast_local_v3_8317.objfilref ofr (cost=0.42..66.20 rows=1 width=12) (actual time=0.204..0.250 rows=1 loops=230,512)

  • Output: ofr.idobj, ofr.idfilref, ofr.idfil
  • Index Cond: (ofr.idobj = kwfsoc.idobj)
  • Filter: (NOT (SubPlan 1))
  • Buffers: shared hit=3533922 read=2119, local read=4
9.          

SubPlan (for Index Scan)

10. 32,498.692 55,697.696 ↓ 0.0 0 321,952

Nested Loop (cost=0.85..96.78 rows=3 width=0) (actual time=0.173..0.173 rows=0 loops=321,952)

  • Join Filter: (op_1.idpro = kwfp.idpro)
  • Rows Removed by Join Filter: 1288
  • Buffers: shared hit=2613522 read=1220, local read=4
11. 1,446.294 6,439.040 ↑ 1.5 2 321,952

Nested Loop (cost=0.85..48.68 rows=3 width=8) (actual time=0.011..0.020 rows=2 loops=321,952)

  • Output: op_1.idpro, opf.idpro
  • Join Filter: (op_1.idpro = opf.idpro)
  • Rows Removed by Join Filter: 57
  • Buffers: shared hit=2613522 read=1220
12. 1,931.712 1,931.712 ↑ 5.6 5 321,952

Index Scan using objpro_obj on diw_castoncast_local_v3_8317.objpro op_1 (cost=0.43..18.43 rows=28 width=4) (actual time=0.005..0.006 rows=5 loops=321,952)

  • Output: op_1.idobj, op_1.idpro, op_1.prop
  • Index Cond: (op_1.idobj = ofr.idobj)
  • Buffers: shared hit=1312530 read=901
13. 1,451.274 3,061.034 ↑ 2.3 12 1,530,517

Materialize (cost=0.43..18.57 rows=28 width=4) (actual time=0.001..0.002 rows=12 loops=1,530,517)

  • Output: opf.idpro
  • Buffers: shared hit=1300992 read=319
14. 1,609.760 1,609.760 ↑ 9.3 3 321,952

Index Scan using objpro_obj on diw_castoncast_local_v3_8317.objpro opf (cost=0.43..18.43 rows=28 width=4) (actual time=0.004..0.005 rows=3 loops=321,952)

  • Output: opf.idpro
  • Index Cond: (opf.idobj = ofr.idfil)
  • Buffers: shared hit=1300992 read=319
15. 16,759.831 16,759.964 ↑ 1.0 767 540,644

Materialize (cost=0.00..15.50 rows=767 width=4) (actual time=0.000..0.031 rows=767 loops=540,644)

  • Output: kwfp.idpro
  • Buffers: local read=4
16. 0.133 0.133 ↑ 1.0 767 1

Seq Scan on pg_temp_6.kb_work_foreignprojects kwfp (cost=0.00..11.67 rows=767 width=4) (actual time=0.042..0.133 rows=767 loops=1)

  • Output: kwfp.idpro
  • Buffers: local read=4
17. 155.871 193.673 ↑ 1.1 321,922 1

Sort (cost=38,361.97..39,257.02 rows=358,020 width=12) (actual time=155.848..193.673 rows=321,922 loops=1)

  • Output: tofr.idobj, tofr.idfilref, tofr.idfil
  • Sort Key: tofr.idobj, tofr.idfilref, tofr.idfil
  • Sort Method: quicksort Memory: 27501kB
  • Buffers: local hit=1315 read=440 written=291
18. 37.802 37.802 ↑ 1.1 324,537 1

Seq Scan on pg_temp_6.tmp_scopeobjfilref tofr (cost=0.00..5,335.20 rows=358,020 width=12) (actual time=0.083..37.802 rows=324,537 loops=1)

  • Output: tofr.idobj, tofr.idfilref, tofr.idfil
  • Buffers: local hit=1315 read=440 written=291
19. 92.908 460.763 ↑ 1.6 472,419 1

Hash (cost=42,175.01..42,175.01 rows=767,716 width=4) (actual time=460.763..460.763 rows=472,419 loops=1)

  • Output: op.idobj
  • Buckets: 1048576 Batches: 1 Memory Usage: 24801kB
  • Buffers: shared hit=8300, local read=4
20. 233.497 367.855 ↑ 1.6 472,419 1

Hash Join (cost=41.73..42,175.01 rows=767,716 width=4) (actual time=25.519..367.855 rows=472,419 loops=1)

  • Output: op.idobj
  • Hash Cond: (op.idpro = kwsp.idpro)
  • Buffers: shared hit=8300, local read=4
21. 133.945 133.945 ↑ 1.0 1,535,432 1

Seq Scan on diw_castoncast_local_v3_8317.objpro op (cost=0.00..23,654.32 rows=1,535,432 width=8) (actual time=0.042..133.945 rows=1,535,432 loops=1)

  • Output: op.idobj, op.idpro, op.prop
  • Buffers: shared hit=8300
22. 0.052 0.413 ↑ 1.9 353 1

Hash (cost=33.16..33.16 rows=686 width=4) (actual time=0.412..0.413 rows=353 loops=1)

  • Output: kwsp.idpro
  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
  • Buffers: local read=4
23. 0.154 0.361 ↑ 1.9 353 1

HashAggregate (cost=19.43..26.29 rows=686 width=4) (actual time=0.326..0.361 rows=353 loops=1)

  • Output: kwsp.idpro
  • Group Key: kwsp.idpro
  • Buffers: local read=4
24. 0.053 0.207 ↑ 1.0 686 1

Append (cost=0.00..17.72 rows=686 width=4) (actual time=0.042..0.207 rows=686 loops=1)

  • Buffers: local read=4
25. 0.088 0.088 ↑ 1.0 340 1

Seq Scan on pg_temp_6.kb_work_scopeprojects kwsp (cost=0.00..5.40 rows=340 width=4) (actual time=0.042..0.088 rows=340 loops=1)

  • Output: kwsp.idpro
  • Buffers: local read=2
26. 0.066 0.066 ↑ 1.0 346 1

Seq Scan on pg_temp_6.kb_work_scopemainprojects kwsmp (cost=0.00..5.46 rows=346 width=4) (actual time=0.030..0.066 rows=346 loops=1)

  • Output: kwsmp.idpro
  • Buffers: local read=2