explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QH8K

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 18,956.710 315,540.195 ↓ 30,072.0 541,296 1

Hash Join (cost=87,734.96..96,441.00 rows=18 width=44) (actual time=295,941.772..315,540.195 rows=541,296 loops=1)

  • Output: ka.idacc, alp.positionmode, ((alp.position1 - kiop.info1) + 1), CASE WHEN (alp.position1 = kiop.info1) THEN ((alp.position2 - kiop.info2) + 1) ELSE alp.position2 END, CASE WHEN (alp.position3 = (-1)) THEN alp.position3 ELSE ((alp.position3 - kiop.info1) + 1) END, CASE WHEN ((alp.position3 = kiop.info1) AND (alp.position4 <> (-1))) THEN ((alp.position4 - kiop.info2) + 1) ELSE alp.position4 END, kiop.prop, kiop.idobjref, ka.isprototype
  • Hash Cond: (ka.idacc = kwli.idacc)
  • Buffers: local hit=2579089 read=20156
2.          

CTE kbacc

3. 77.900 384.575 ↑ 1.0 355,258 1

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

  • Output: kia.idacc, kia.idclr, kia.idcle, kia.isprototype
  • Buffers: local read=3321
4. 49.932 306.675 ↑ 1.0 355,258 1

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

  • Buffers: local read=3321
5. 256.742 256.742 ↑ 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.030..256.742 rows=355,258 loops=1)

  • Output: kia.idacc, kia.idclr, kia.idcle, kia.isprototype
  • Buffers: local read=3321
6. 0.001 0.001 ↓ 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.001..0.001 rows=0 loops=1)

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

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

  • Output: ka.idacc, ka.idclr, ka.idcle, ka.isprototype
  • Buffers: local read=3321
8. 286.755 295,941.549 ↓ 60,144.0 541,296 1

Hash (cost=80,738.44..80,738.44 rows=9 width=40) (actual time=295,941.549..295,941.549 rows=541,296 loops=1)

  • Output: kwli.idacc, alp.positionmode, alp.position1, alp.position2, alp.position3, alp.position4, kiop.info1, kiop.info2, kiop.prop, kiop.idobjref
  • Buckets: 1024 Batches: 1 Memory Usage: 38060kB
  • Buffers: local hit=2579089 read=16835
9. 530.991 295,654.794 ↓ 60,144.0 541,296 1

Nested Loop (cost=21,503.36..80,738.44 rows=9 width=40) (actual time=265.995..295,654.794 rows=541,296 loops=1)

  • Output: kwli.idacc, alp.positionmode, alp.position1, alp.position2, alp.position3, alp.position4, kiop.info1, kiop.info2, kiop.prop, kiop.idobjref
  • Buffers: local hit=2579089 read=16835
10. 215,676.364 294,035.867 ↓ 60,440.9 543,968 1

Hash Join (cost=21,503.36..80,735.41 rows=9 width=44) (actual time=265.972..294,035.867 rows=543,968 loops=1)

  • Output: al.linkid, alp.positionmode, alp.position1, alp.position2, alp.position3, alp.position4, alp.linkid, kiop.info1, kiop.info2, kiop.prop, kiop.idobjref
  • Hash Cond: ((alp.linkid = al.linkid) AND (kwoir.objectid = al.callerid) AND (kwoir.objectkind = al.callerkind))
  • Buffers: local hit=407693 read=12062
11. 63,274.238 78,095.480 ↓ 291.0 178,896,522 1

Merge Join (cost=503.04..34,379.77 rows=614,672 width=46) (actual time=0.191..78,095.480 rows=178,896,522 loops=1)

  • Output: alp.positionmode, alp.position1, alp.position2, alp.position3, alp.position4, alp.linkid, kiop.info1, kiop.info2, kiop.prop, kiop.idobjref, kwoir.objectid, kwoir.objectkind
  • Merge Cond: ((kwois.objectid = alp.sourceid) AND (kwois.objectkind = alp.sourcekind))
  • Buffers: local hit=407689 read=7635
12. 108.531 750.949 ↑ 1.0 72,826 1

Nested Loop (cost=0.00..113,341.46 rows=74,145 width=28) (actual time=0.142..750.949 rows=72,826 loops=1)

  • Output: kwoir.objectid, kwoir.objectkind, kiop.info1, kiop.info2, kiop.prop, kiop.idobjref, kwois.objectid, kwois.objectkind
  • Buffers: local hit=402312 read=1710
13. 51.172 209.362 ↑ 1.0 72,176 1

Nested Loop (cost=0.00..91,426.12 rows=73,085 width=26) (actual time=0.134..209.362 rows=72,176 loops=1)

  • Output: kwois.objectid, kwois.objectkind, kiop.info1, kiop.info2, kiop.prop, kiop.idobjref, kiop.idobj
  • Buffers: local hit=185339 read=1140
14. 17.322 17.322 ↑ 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.010..17.322 rows=70,434 loops=1)

  • Output: kwois.objectid, kwois.objectkind, kwois.idkey
  • Buffers: local hit=42146 read=342
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=20) (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=143193 read=798
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=216973 read=570
17. 13,914.661 14,070.293 ↓ 335.4 178,896,517 1

Materialize (cost=0.00..19,682.13 rows=533,325 width=30) (actual time=0.045..14,070.293 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=5377 read=5925
18. 155.632 155.632 ↑ 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=30) (actual time=0.040..155.632 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=5377 read=5925
19. 161.918 264.023 ↑ 1.0 602,521 1

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

  • Output: al.linkid, al.callerid, al.callerkind
  • Buckets: 65536 Batches: 1 Memory Usage: 24713kB
  • Buffers: local hit=4 read=4427
20. 102.105 102.105 ↑ 1.0 602,521 1

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

  • Output: al.linkid, al.callerid, al.callerkind
  • Buffers: local hit=4 read=4427
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=8) (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=2171396 read=4773