explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Jtr : Optimization for: plan #gFq6

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 803.530 3,774.006 ↑ 2.2 530,596 1

Hash Join (cost=215,752.24..314,445.83 rows=1,183,971 width=110) (actual time=1,718.376..3,774.006 rows=530,596 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
  • Hash Cond: (kwli.linkid = al.linkid)
  • Buffers: local hit=1711 read=17496, temp read=3518 written=3518
2.          

CTE kbacc

3. 27.495 115.681 ↑ 1.0 355,258 1

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

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

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

  • Buffers: local read=3321
5. 69.314 69.314 ↑ 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.011..69.314 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. 805.464 2,356.017 ↑ 2.2 530,596 1

Hash Join (cost=119,356.45..191,114.15 rows=1,185,728 width=74) (actual time=1,103.631..2,356.017 rows=530,596 loops=1)

  • Output: ka.idacc, ka.idclr, ka.idcle, ka.isprototype, kwli.linkid, kwli.linkkind, kwli.idacc, 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
  • Hash Cond: (kwli.linkid = alp.linkid)
  • Buffers: local hit=1708 read=12687, temp read=3518 written=3518
8. 214.330 691.741 ↑ 2.3 599,691 1

Merge Join (cost=41,205.71..82,376.28 rows=1,362,171 width=26) (actual time=244.779..691.741 rows=599,691 loops=1)

  • Output: ka.idacc, ka.idclr, ka.idcle, ka.isprototype, kwli.linkid, kwli.linkkind, kwli.idacc
  • Merge Cond: (kwli.idacc = ka.idacc)
  • Buffers: local hit=1 read=8205
9. 196.690 196.690 ↑ 1.1 599,692 1

Index Scan using idx_kb_work_linkids_idacc on pg_temp_3.kb_work_linkids kwli (cost=0.00..19,060.61 rows=670,955 width=10) (actual time=0.052..196.690 rows=599,692 loops=1)

  • Output: kwli.linkid, kwli.linkkind, kwli.idacc
  • Filter: ('S'::bpchar = kwli.linkkind)
  • Buffers: local hit=1 read=4884
10. 78.688 280.721 ↓ 1.6 599,691 1

Sort (cost=41,205.71..42,122.06 rows=366,540 width=16) (actual time=244.722..280.721 rows=599,691 loops=1)

  • Output: ka.idacc, ka.idclr, ka.idcle, ka.isprototype
  • Sort Key: ka.idacc
  • Sort Method: quicksort Memory: 28941kB
  • Buffers: local read=3321
11. 202.033 202.033 ↑ 1.0 355,258 1

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

  • Output: ka.idacc, ka.idclr, ka.idcle, ka.isprototype
  • Buffers: local read=3321
12. 166.576 858.812 ↑ 1.1 533,325 1

Hash (cost=70,850.16..70,850.16 rows=584,046 width=48) (actual time=858.812..858.812 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, kwois.objectid, kwois.objectkind, kwois.idkey
  • Buckets: 65536 Batches: 1 Memory Usage: 43750kB
  • Buffers: local hit=1707 read=4482, temp read=3518 written=3518
13. 124.140 692.236 ↑ 1.1 533,325 1

Merge Join (cost=60,522.19..70,850.16 rows=584,046 width=48) (actual time=495.937..692.236 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, kwois.objectid, kwois.objectkind, kwois.idkey
  • Merge Cond: ((kwois.objectid = alp.sourceid) AND (kwois.objectkind = alp.sourcekind))
  • Buffers: local hit=1707 read=4482, temp read=3518 written=3518
14. 1.677 1.677 ↑ 19.4 3,636 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.023..1.677 rows=3,636 loops=1)

  • Output: kwois.objectid, kwois.objectkind, kwois.idkey
  • Buffers: local hit=1706 read=37
15. 505.034 566.419 ↑ 1.0 533,325 1

Sort (cost=60,510.87..61,844.18 rows=533,325 width=38) (actual time=495.850..566.419 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
  • Sort Key: alp.sourceid, alp.sourcekind
  • Sort Method: external sort Disk: 28144kB
  • Buffers: local hit=1 read=4445, temp read=3518 written=3518
16. 61.385 61.385 ↑ 1.0 533,325 1

Seq Scan on pg_temp_3.amt_linkpositions alp (cost=0.00..9,779.25 rows=533,325 width=38) (actual time=0.003..61.385 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=1 read=4445
17. 204.087 614.459 ↓ 1.0 602,521 1

Hash (cost=81,879.04..81,879.04 rows=601,628 width=36) (actual time=614.459..614.459 rows=602,521 loops=1)

  • Output: al.linkid, al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, al.linktypeid, kwoir.objectid, kwoir.objectkind, kwoir.idkey
  • Buckets: 65536 Batches: 1 Memory Usage: 44719kB
  • Buffers: local hit=3 read=4809
18. 306.628 410.372 ↓ 1.0 602,521 1

Hash Join (cost=2,141.85..81,879.04 rows=601,628 width=36) (actual time=32.991..410.372 rows=602,521 loops=1)

  • Output: al.linkid, al.callerid, al.callerkind, al.calleeid, al.calleekind, al.projectid, al.projectkind, al.linktypeid, kwoir.objectid, kwoir.objectkind, kwoir.idkey
  • Hash Cond: ((al.callerid = kwoir.objectid) AND (al.callerkind = kwoir.objectkind))
  • Buffers: local hit=3 read=4809
19. 70.777 70.777 ↑ 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.005..70.777 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=1 read=4430
20. 17.516 32.967 ↑ 1.0 70,434 1

Hash (cost=1,085.34..1,085.34 rows=70,434 width=10) (actual time=32.967..32.967 rows=70,434 loops=1)

  • Output: kwoir.objectid, kwoir.objectkind, kwoir.idkey
  • Buckets: 8192 Batches: 1 Memory Usage: 3027kB
  • Buffers: local hit=2 read=379
21. 15.451 15.451 ↑ 1.0 70,434 1

Seq Scan on pg_temp_3.kb_work_objectids kwoir (cost=0.00..1,085.34 rows=70,434 width=10) (actual time=0.025..15.451 rows=70,434 loops=1)

  • Output: kwoir.objectid, kwoir.objectkind, kwoir.idkey
  • Buffers: local hit=2 read=379