explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6IMk

Settings
# exclusive inclusive rows x rows loops node
1. 7,663.120 288,137.482 ↓ 30,072.0 541,296 1

Hash Join (cost=87,734.96..96,440.46 rows=18 width=146) (actual time=280,097.183..288,137.482 rows=541,296 loops=1)

  • Output: ka.idacc, ka.idclr, ka.idcle, ka.isprototype, kwli.linkid, kwli.linkkind, kwli.idacc, al.linkid, al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, al.linktypeid, alp.linkid, alp.sourceid, alp.sourcekind, alp.sequencenumber, alp.positionmode, alp.position1, alp.position2, alp.position3, alp.position4, alp.groupnumber, kwoir.objectid, kwoir.objectkind, kwoir.idkey, kwois.objectid, kwois.objectkind, kwois.idkey, kiop.idobj, kiop.idobjref, kiop.posmode, kiop.info1, kiop.info2, kiop.info3, kiop.info4, kiop.prop, kiop.blkno
  • Hash Cond: (ka.idacc = kwli.idacc)
  • Buffers: local hit=2579064 read=20181, temp read=6207 written=6205
2.          

CTE kbacc

3. 45.794 223.798 ↑ 1.0 355,258 1

Result (cost=0.00..6,996.40 rows=366,540 width=16) (actual time=0.012..223.798 rows=355,258 loops=1)

  • Output: kia.idacc, kia.idclr, kia.idcle, kia.isprototype
  • Buffers: local hit=1 read=3320
4. 30.043 178.004 ↑ 1.0 355,258 1

Append (cost=0.00..6,996.40 rows=366,540 width=16) (actual time=0.011..178.004 rows=355,258 loops=1)

  • Buffers: local hit=1 read=3320
5. 147.961 147.961 ↑ 1.0 355,258 1

Seq Scan on pg_temp_3.kb_insertacc kia (cost=0.00..6,974.10 rows=365,310 width=16) (actual time=0.010..147.961 rows=355,258 loops=1)

  • Output: kia.idacc, kia.idclr, kia.idcle, kia.isprototype
  • Buffers: local hit=1 read=3320
6. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on pg_temp_3.kb_updateacc kua (cost=0.00..22.30 rows=1,230 width=16) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: kua.idacc, kua.idclr, kua.idcle, kua.isprototype
7. 377.664 377.664 ↑ 1.0 355,258 1

CTE Scan on kbacc ka (cost=0.00..7,330.80 rows=366,540 width=16) (actual time=0.017..377.664 rows=355,258 loops=1)

  • Output: ka.idacc, ka.idclr, ka.idcle, ka.isprototype
  • Buffers: local hit=1 read=3320
8. 616.116 280,096.698 ↓ 60,144.0 541,296 1

Hash (cost=80,738.44..80,738.44 rows=9 width=130) (actual time=280,096.698..280,096.698 rows=541,296 loops=1)

  • Output: kwli.linkid, kwli.linkkind, kwli.idacc, al.linkid, al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, al.linktypeid, alp.linkid, alp.sourceid, alp.sourcekind, alp.sequencenumber, alp.positionmode, alp.position1, alp.position2, alp.position3, alp.position4, alp.groupnumber, kwoir.objectid, kwoir.objectkind, kwoir.idkey, kwois.objectid, kwois.objectkind, kwois.idkey, kiop.idobj, kiop.idobjref, kiop.posmode, kiop.info1, kiop.info2, kiop.info3, kiop.info4, kiop.prop, kiop.blkno
  • Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 65537kB
  • Buffers: local hit=2579063 read=16861, temp written=5424
9. 481.251 279,480.582 ↓ 60,144.0 541,296 1

Nested Loop (cost=21,503.36..80,738.44 rows=9 width=130) (actual time=202.506..279,480.582 rows=541,296 loops=1)

  • Output: kwli.linkid, kwli.linkkind, kwli.idacc, al.linkid, al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, al.linktypeid, alp.linkid, alp.sourceid, alp.sourcekind, alp.sequencenumber, alp.positionmode, alp.position1, alp.position2, alp.position3, alp.position4, alp.groupnumber, kwoir.objectid, kwoir.objectkind, kwoir.idkey, kwois.objectid, kwois.objectkind, kwois.idkey, kiop.idobj, kiop.idobjref, kiop.posmode, kiop.info1, kiop.info2, kiop.info3, kiop.info4, kiop.prop, kiop.blkno
  • Buffers: local hit=2579063 read=16861
10. 201,659.637 277,911.395 ↓ 60,440.9 543,968 1

Hash Join (cost=21,503.36..80,735.41 rows=9 width=120) (actual time=202.482..277,911.395 rows=543,968 loops=1)

  • Output: al.linkid, al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, al.linktypeid, alp.linkid, alp.sourceid, alp.sourcekind, alp.sequencenumber, alp.positionmode, alp.position1, alp.position2, alp.position3, alp.position4, alp.groupnumber, kwoir.objectid, kwoir.objectkind, kwoir.idkey, kwois.objectid, kwois.objectkind, kwois.idkey, kiop.idobj, kiop.idobjref, kiop.posmode, kiop.info1, kiop.info2, kiop.info3, kiop.info4, kiop.prop, kiop.blkno
  • Hash Cond: ((alp.linkid = al.linkid) AND (kwoir.objectid = al.callerid) AND (kwoir.objectkind = al.callerkind))
  • Buffers: local hit=407652 read=12103
11. 62,411.761 76,052.711 ↓ 291.0 178,896,522 1

Merge Join (cost=503.04..34,379.77 rows=614,672 width=94) (actual time=1.882..76,052.711 rows=178,896,522 loops=1)

  • Output: alp.linkid, alp.sourceid, alp.sourcekind, alp.sequencenumber, alp.positionmode, alp.position1, alp.position2, alp.position3, alp.position4, alp.groupnumber, kwois.objectid, kwois.objectkind, kwois.idkey, kiop.idobj, kiop.idobjref, kiop.posmode, kiop.info1, kiop.info2, kiop.info3, kiop.info4, kiop.prop, kiop.blkno, kwoir.objectid, kwoir.objectkind, kwoir.idkey
  • Merge Cond: ((kwois.objectid = alp.sourceid) AND (kwois.objectkind = alp.sourcekind))
  • Buffers: local hit=407650 read=7674
12. 121.117 757.289 ↑ 1.0 72,826 1

Nested Loop (cost=0.00..113,341.46 rows=74,145 width=56) (actual time=1.853..757.289 rows=72,826 loops=1)

  • Output: kwoir.objectid, kwoir.objectkind, kwoir.idkey, kiop.idobj, kiop.idobjref, kiop.posmode, kiop.info1, kiop.info2, kiop.info3, kiop.info4, kiop.prop, kiop.blkno, kwois.objectid, kwois.objectkind, kwois.idkey
  • Buffers: local hit=402274 read=1748
13. 44.645 203.116 ↑ 1.0 72,176 1

Nested Loop (cost=0.00..91,426.12 rows=73,085 width=46) (actual time=0.281..203.116 rows=72,176 loops=1)

  • Output: kwois.objectid, kwois.objectkind, kwois.idkey, kiop.idobj, kiop.idobjref, kiop.posmode, kiop.info1, kiop.info2, kiop.info3, kiop.info4, kiop.prop, kiop.blkno
  • Buffers: local hit=185326 read=1153
14. 17.603 17.603 ↑ 1.0 70,434 1

Index Scan using idx_kb_work_objectids_objectid_objectkind on pg_temp_3.kb_work_objectids kwois (cost=0.00..2,202.22 rows=70,434 width=10) (actual time=0.013..17.603 rows=70,434 loops=1)

  • Output: kwois.objectid, kwois.objectkind, kwois.idkey
  • Buffers: local hit=42138 read=350
15. 140.868 140.868 ↑ 33.0 1 70,434

Index Scan using idx_kb_insertobjpos_idobjref on pg_temp_3.kb_insertobjpos kiop (cost=0.00..0.94 rows=33 width=36) (actual time=0.001..0.002 rows=1 loops=70,434)

  • Output: kiop.idobj, kiop.idobjref, kiop.posmode, kiop.info1, kiop.info2, kiop.info3, kiop.info4, kiop.prop, kiop.blkno
  • Index Cond: (kiop.idobjref = kwois.idkey)
  • Buffers: local hit=143188 read=803
16. 433.056 433.056 ↑ 1.0 1 72,176

Index Scan using idx_kb_work_objectids_idkey on pg_temp_3.kb_work_objectids kwoir (cost=0.00..0.29 rows=1 width=10) (actual time=0.005..0.006 rows=1 loops=72,176)

  • Output: kwoir.objectid, kwoir.objectkind, kwoir.idkey
  • Index Cond: (kwoir.idkey = kiop.idobj)
  • Buffers: local hit=216948 read=595
17. 12,735.491 12,883.661 ↓ 335.4 178,896,517 1

Materialize (cost=0.00..19,682.13 rows=533,325 width=38) (actual time=0.019..12,883.661 rows=178,896,517 loops=1)

  • Output: alp.linkid, alp.sourceid, alp.sourcekind, alp.sequencenumber, alp.positionmode, alp.position1, alp.position2, alp.position3, alp.position4, alp.groupnumber
  • Buffers: local hit=5376 read=5926
18. 148.170 148.170 ↑ 1.0 533,325 1

Index Scan using idx_amt_linkpositions_sourceid_sourcekind on pg_temp_3.amt_linkpositions alp (cost=0.00..18,348.82 rows=533,325 width=38) (actual time=0.016..148.170 rows=533,325 loops=1)

  • Output: alp.linkid, alp.sourceid, alp.sourcekind, alp.sequencenumber, alp.positionmode, alp.position1, alp.position2, alp.position3, alp.position4, alp.groupnumber
  • Buffers: local hit=5376 read=5926
19. 131.632 199.047 ↑ 1.0 602,521 1

Hash (cost=10,456.21..10,456.21 rows=602,521 width=26) (actual time=199.047..199.047 rows=602,521 loops=1)

  • Output: al.linkid, al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, al.linktypeid
  • Buckets: 65536 Batches: 1 Memory Usage: 37658kB
  • Buffers: local hit=2 read=4429
20. 67.415 67.415 ↑ 1.0 602,521 1

Seq Scan on pg_temp_3.amt_links al (cost=0.00..10,456.21 rows=602,521 width=26) (actual time=0.006..67.415 rows=602,521 loops=1)

  • Output: al.linkid, al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, al.linktypeid
  • Buffers: local hit=2 read=4429
21. 1,087.936 1,087.936 ↑ 1.0 1 543,968

Index Scan using idx_kb_work_linkids_linkid_linkkind on pg_temp_3.kb_work_linkids kwli (cost=0.00..0.33 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=543,968)

  • Output: kwli.linkid, kwli.linkkind, kwli.idacc
  • Index Cond: ((kwli.linkid = al.linkid) AND ('S'::bpchar = kwli.linkkind))
  • Buffers: local hit=2171411 read=4758