explain.depesz.com

PostgreSQL's explain analyze made readable

Result: abw5 : Optimization for: plan #QH8K

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 16,433.517 290,968.744 ↓ 30,072.0 541,296 1

Hash Join (cost=87,734.96..96,441.00 rows=18 width=44) (actual time=274,083.013..290,968.744 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=2579047 read=20198
2.          

CTE kbacc

3. 51.453 272.076 ↑ 1.0 355,258 1

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

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

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

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

  • Output: kia.idacc, kia.idclr, kia.idcle, kia.isprototype
  • Buffers: local read=3321
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. 452.399 452.399 ↑ 1.0 355,258 1

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

  • Output: ka.idacc, ka.idclr, ka.idcle, ka.isprototype
  • Buffers: local read=3321
8. 274.936 274,082.828 ↓ 60,144.0 541,296 1

Hash (cost=80,738.44..80,738.44 rows=9 width=40) (actual time=274,082.828..274,082.828 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=2579047 read=16877
9. 437.264 273,807.892 ↓ 60,144.0 541,296 1

Nested Loop (cost=21,503.36..80,738.44 rows=9 width=40) (actual time=240.337..273,807.892 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=2579047 read=16877
10. 200,529.784 272,282.692 ↓ 60,440.9 543,968 1

Hash Join (cost=21,503.36..80,735.41 rows=9 width=44) (actual time=240.315..272,282.692 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=407652 read=12103
11. 57,876.971 71,514.076 ↓ 291.0 178,896,522 1

Merge Join (cost=503.04..34,379.77 rows=614,672 width=46) (actual time=0.134..71,514.076 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=407650 read=7674
12. 80.330 630.485 ↑ 1.0 72,826 1

Nested Loop (cost=0.00..113,341.46 rows=74,145 width=28) (actual time=0.123..630.485 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=402273 read=1749
13. 31.964 189.275 ↑ 1.0 72,176 1

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

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

  • Output: kwois.objectid, kwois.objectkind, kwois.idkey
  • Buffers: local hit=42139 read=349
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=143187 read=804
16. 360.880 360.880 ↑ 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.004..0.005 rows=1 loops=72,176)

  • Output: kwoir.objectid, kwoir.objectkind, kwoir.idkey
  • Index Cond: (kwoir.idkey = kiop.idobj)
  • Buffers: local hit=216947 read=596
17. 12,858.620 13,006.620 ↓ 335.4 178,896,517 1

Materialize (cost=0.00..19,682.13 rows=533,325 width=30) (actual time=0.008..13,006.620 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. 148.000 148.000 ↑ 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.006..148.000 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. 145.006 238.832 ↑ 1.0 602,521 1

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

  • Output: al.linkid, al.callerid, al.callerkind
  • Buckets: 65536 Batches: 1 Memory Usage: 24713kB
  • Buffers: local hit=2 read=4429
20. 93.826 93.826 ↑ 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.004..93.826 rows=602,521 loops=1)

  • Output: al.linkid, al.callerid, al.callerkind
  • 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=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=2171395 read=4774