explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MhrW

Settings
# exclusive inclusive rows x rows loops node
1. 60.590 163,829.302 ↓ 0.0 0 1

Insert on pg_temp_12.kb_work_fullscopeobjects (cost=4,785,895.91..4,791,893.47 rows=299,878 width=16) (actual time=163,829.302..163,829.302 rows=0 loops=1)

  • Buffers: shared hit=1,591,248 read=3,677, local hit=1,205,831 read=13,806 dirtied=824 written=738, temp read=3,399 written=3,393
2. 35.603 163,768.712 ↑ 2.0 152,257 1

Unique (cost=4,785,895.91..4,788,894.69 rows=299,878 width=16) (actual time=163,695.979..163,768.712 rows=152,257 loops=1)

  • Output: kwoi.objectid, kwoi.objectkind, kwoi.idkey
  • Buffers: shared hit=1,591,248 read=3,677, local hit=1,051,931 read=12,980 written=738, temp read=3,399 written=3,393
3. 142.641 163,733.109 ↑ 1.6 190,398 1

Sort (cost=4,785,895.91..4,786,645.60 rows=299,878 width=16) (actual time=163,695.977..163,733.109 rows=190,398 loops=1)

  • Output: kwoi.objectid, kwoi.objectkind, kwoi.idkey
  • Sort Key: kwoi.objectid, kwoi.objectkind, kwoi.idkey
  • Sort Method: external merge Disk: 4,096kB
  • Buffers: shared hit=1,591,248 read=3,677, local hit=1,051,931 read=12,980 written=738, temp read=3,399 written=3,393
4. 15.253 163,590.468 ↑ 1.6 190,398 1

Append (cost=38,228.54..4,753,488.48 rows=299,878 width=16) (actual time=487.936..163,590.468 rows=190,398 loops=1)

  • Buffers: shared hit=1,591,248 read=3,677, local hit=1,051,931 read=12,980 written=738, temp read=2,886 written=2,880
5. 28.344 541.743 ↑ 1.1 133,724 1

Unique (cost=38,228.54..39,697.18 rows=146,864 width=10) (actual time=487.935..541.743 rows=133,724 loops=1)

  • Output: kwoi.objectid, kwoi.objectkind, kwoi.idkey
  • Buffers: local hit=4 read=5,207 written=9, temp read=1,173 written=1,167
6. 127.634 513.399 ↑ 1.0 146,689 1

Sort (cost=38,228.54..38,595.70 rows=146,864 width=10) (actual time=487.933..513.399 rows=146,689 loops=1)

  • Output: kwoi.objectid, kwoi.objectkind, kwoi.idkey
  • Sort Key: kwoi.objectid, kwoi.objectkind, kwoi.idkey
  • Sort Method: external merge Disk: 3,152kB
  • Buffers: local hit=4 read=5,207 written=9, temp read=1,173 written=1,167
7. 156.239 385.765 ↑ 1.0 146,689 1

Hash Join (cost=5,165.82..23,111.58 rows=146,864 width=10) (actual time=75.749..385.765 rows=146,689 loops=1)

  • Output: kwoi.objectid, kwoi.objectkind, kwoi.idkey
  • Hash Cond: (kwop.idobj = kwoi.idkey)
  • Buffers: local hit=4 read=5,207 written=9, temp read=778 written=772
8. 156.352 156.352 ↑ 1.0 146,689 1

Seq Scan on pg_temp_12.kb_work_objpro kwop (cost=0.00..14,030.39 rows=146,864 width=4) (actual time=0.020..156.352 rows=146,689 loops=1)

  • Output: kwop.idobj
  • Filter: (kwop.prop = 0)
  • Rows Removed by Filter: 636,862
  • Buffers: local hit=2 read=4,234 written=9
9. 40.592 73.174 ↑ 1.0 153,014 1

Hash (cost=2,505.14..2,505.14 rows=153,014 width=10) (actual time=73.174..73.174 rows=153,014 loops=1)

  • Output: kwoi.objectid, kwoi.objectkind, kwoi.idkey
  • Buckets: 131,072 Batches: 4 Memory Usage: 2,686kB
  • Buffers: local hit=2 read=973, temp written=446
10. 32.582 32.582 ↑ 1.0 153,014 1

Seq Scan on pg_temp_12.kb_work_objectids kwoi (cost=0.00..2,505.14 rows=153,014 width=10) (actual time=0.043..32.582 rows=153,014 loops=1)

  • Output: kwoi.objectid, kwoi.objectkind, kwoi.idkey
  • Buffers: local hit=2 read=973
11. 97.749 163,033.472 ↑ 2.7 56,674 1

Unique (cost=4,707,609.08..4,710,792.52 rows=153,014 width=10) (actual time=162,786.158..163,033.472 rows=56,674 loops=1)

  • Output: kwoi_1.objectid, kwoi_1.objectkind, kwoi_1.idkey
  • Buffers: shared hit=1,591,248 read=3,677, local hit=1,051,927 read=7,773 written=729, temp read=1,713 written=1,713
12. 556.764 162,935.723 ↓ 2.0 636,862 1

Sort (cost=4,707,609.08..4,708,404.94 rows=318,344 width=10) (actual time=162,786.156..162,935.723 rows=636,862 loops=1)

  • Output: kwoi_1.objectid, kwoi_1.objectkind, kwoi_1.idkey
  • Sort Key: kwoi_1.objectid, kwoi_1.objectkind, kwoi_1.idkey
  • Sort Method: external merge Disk: 13,616kB
  • Buffers: shared hit=1,591,248 read=3,677, local hit=1,051,927 read=7,773 written=729, temp read=1,713 written=1,713
13. 195.742 162,378.959 ↓ 2.0 636,862 1

Merge Join (cost=14,053.37..4,673,069.58 rows=318,344 width=10) (actual time=185.944..162,378.959 rows=636,862 loops=1)

  • Output: kwoi_1.objectid, kwoi_1.objectkind, kwoi_1.idkey
  • Merge Cond: (kwoi_1.idkey = kwop_1.idobj)
  • Buffers: shared hit=1,591,248 read=3,677, local hit=1,051,927 read=7,773 written=729
14. 1,541.053 161,745.524 ↓ 2.0 152,254 1

Merge Left Join (cost=13,266.48..4,641,547.59 rows=76,507 width=10) (actual time=133.880..161,745.524 rows=152,254 loops=1)

  • Output: kwoi_1.objectid, kwoi_1.objectkind, kwoi_1.idkey
  • Merge Cond: (kwoi_1.idkey = k.idkey)
  • Filter: ((k.idkey IS NULL) OR (NOT (SubPlan 1)))
  • Rows Removed by Filter: 757
  • Buffers: shared hit=1,591,248 read=3,677, local hit=1,051,923 read=1,398 written=173
15. 231.933 231.933 ↑ 1.0 153,011 1

Index Scan using idx_kb_work_objectids_idkey on pg_temp_12.kb_work_objectids kwoi_1 (cost=0.42..7,884.88 rows=153,014 width=10) (actual time=0.023..231.933 rows=153,011 loops=1)

  • Output: kwoi_1.objectid, kwoi_1.objectkind, kwoi_1.idkey, kwoi_1.objtyp, kwoi_1.isexisting
  • Buffers: local hit=139,613 read=1,392 written=173
16. 571.486 571.486 ↑ 1.0 521,251 1

Index Only Scan using pk_keysidkey on castoncast_local.keys k (cost=0.42..38,895.25 rows=521,649 width=4) (actual time=0.636..571.486 rows=521,251 loops=1)

  • Output: k.idkey
  • Heap Fetches: 521,251
  • Buffers: shared hit=374,224 read=1,327
17.          

SubPlan (for Merge Left Join)

18. 144,285.435 159,401.052 ↓ 0.0 0 152,683

Nested Loop (cost=0.43..532.87 rows=18 width=0) (actual time=1.044..1.044 rows=0 loops=152,683)

  • Join Filter: (op.idpro = kwfp.idpro)
  • Rows Removed by Join Filter: 5,988
  • Buffers: shared hit=1,217,024 read=2,350, local hit=912,310 read=6
19. 15,115.617 15,115.617 ↑ 1.0 1,162 152,683

Seq Scan on pg_temp_12.kb_work_foreignprojects kwfp (cost=0.00..17.67 rows=1,167 width=4) (actual time=0.004..0.099 rows=1,162 loops=152,683)

  • Output: kwfp.idpro
  • Buffers: local hit=912,310 read=6
20. 0.000 0.000 ↑ 4.8 5 177,374,829

Materialize (cost=0.43..95.14 rows=24 width=4) (actual time=0.000..0.000 rows=5 loops=177,374,829)

  • Output: op.idpro
  • Buffers: shared hit=1,217,024 read=2,350
21. 1,984.879 1,984.879 ↑ 4.8 5 152,683

Index Scan using objpro_obj on castoncast_local.objpro op (cost=0.43..95.02 rows=24 width=4) (actual time=0.005..0.013 rows=5 loops=152,683)

  • Output: op.idpro
  • Index Cond: (op.idobj = kwoi_1.idkey)
  • Buffers: shared hit=1,217,024 read=2,350
22. 437.693 437.693 ↓ 1.0 636,862 1

Index Scan using idx_kb_work_objpro_idobj on pg_temp_12.kb_work_objpro kwop_1 (cost=0.42..26,555.57 rows=636,687 width=4) (actual time=0.034..437.693 rows=636,862 loops=1)

  • Output: kwop_1.idobj, kwop_1.idpro, kwop_1.prop
  • Filter: (kwop_1.prop <> 0)
  • Rows Removed by Filter: 146,689
  • Buffers: local hit=4 read=6,375 written=556