explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gFq6

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 6,057.836 44,751.703 ↓ 110.3 530,596 1

Hash Join (cost=102,175.05..111,386.65 rows=4,810 width=116) (actual time=38,365.556..44,751.703 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: (ka.idacc = kwli.idacc)
  • Buffers: local hit=1596723 read=16214, temp read=8448 written=8446
2.          

CTE kbacc

3. 37.604 197.834 ↑ 1.0 355,258 1

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

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

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

  • Buffers: local hit=1 read=3320
5. 132.738 132.738 ↑ 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.014..132.738 rows=355,258 loops=1)

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

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

  • Output: ka.idacc, ka.idclr, ka.idcle, ka.isprototype
  • Buffers: local hit=1 read=3320
8. 598.127 38,365.010 ↓ 215.4 530,596 1

Hash (cost=95,147.86..95,147.86 rows=2,463 width=100) (actual time=38,365.010..38,365.010 rows=530,596 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
  • Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 65537kB
  • Buffers: local hit=1596722 read=12894, temp read=3518 written=7665
9. 569.999 37,766.883 ↓ 215.4 530,596 1

Nested Loop (cost=79,876.04..95,147.86 rows=2,463 width=100) (actual time=1,858.498..37,766.883 rows=530,596 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
  • Buffers: local hit=1596722 read=12894, temp read=3518 written=3518
10. 33,950.740 36,135.692 ↓ 215.2 530,596 1

Hash Join (cost=79,876.04..94,430.72 rows=2,466 width=90) (actual time=1,858.485..36,135.692 rows=530,596 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, kwois.objectid, kwois.objectkind, kwois.idkey
  • Hash Cond: (kwli.linkid = al.linkid)
  • Buffers: local hit=1736 read=12511, temp read=3518 written=3518
11. 326.563 326.563 ↑ 1.0 670,955 1

Seq Scan on pg_temp_3.kb_work_linkids kwli (cost=0.00..12,013.94 rows=670,955 width=10) (actual time=0.018..326.563 rows=670,955 loops=1)

  • Output: kwli.linkid, kwli.linkkind, kwli.idacc
  • Filter: ('S'::bpchar = kwli.linkkind)
  • Buffers: local hit=1 read=3626
12. 218.618 1,858.389 ↓ 216.3 533,325 1

Hash (cost=79,845.21..79,845.21 rows=2,466 width=80) (actual time=1,858.389..1,858.389 rows=533,325 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, kwois.objectid, kwois.objectkind, kwois.idkey
  • Buckets: 1024 Batches: 1 Memory Usage: 60416kB
  • Buffers: local hit=1735 read=8885, temp read=3518 written=3518
13. 698.000 1,639.771 ↓ 216.3 533,325 1

Hash Join (cost=73,539.64..79,845.21 rows=2,466 width=80) (actual time=700.247..1,639.771 rows=533,325 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, kwois.objectid, kwois.objectkind, kwois.idkey
  • Hash Cond: (alp.linkid = al.linkid)
  • Buffers: local hit=1735 read=8885, temp read=3518 written=3518
14. 147.986 752.211 ↓ 216.3 533,325 1

Merge Join (cost=55,551.92..61,798.92 rows=2,466 width=54) (actual time=509.974..752.211 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=1734 read=4455, temp read=3518 written=3518
15. 2.943 2.943 ↑ 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.009..2.943 rows=3,636 loops=1)

  • Output: kwois.objectid, kwois.objectkind, kwois.idkey
  • Buffers: local hit=1734 read=9
16. 527.556 601.282 ↓ 1.1 533,325 1

Sort (cost=55,551.92..56,774.57 rows=489,060 width=44) (actual time=509.485..601.282 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 read=4446, temp read=3518 written=3518
17. 73.726 73.726 ↓ 1.1 533,325 1

Seq Scan on pg_temp_3.amt_linkpositions alp (cost=0.00..9,336.60 rows=489,060 width=44) (actual time=0.254..73.726 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 read=4446
18. 111.514 189.560 ↑ 1.0 602,521 1

Hash (cost=10,456.21..10,456.21 rows=602,521 width=26) (actual time=189.560..189.560 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=1 read=4430
19. 78.046 78.046 ↑ 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.012..78.046 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. 1,061.192 1,061.192 ↑ 1.0 1 530,596

Index Scan using idx_kb_work_objectids_objectid_objectkind on pg_temp_3.kb_work_objectids kwoir (cost=0.00..0.28 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=530,596)

  • Output: kwoir.objectid, kwoir.objectkind, kwoir.idkey
  • Index Cond: ((kwoir.objectid = al.callerid) AND (kwoir.objectkind = al.callerkind))
  • Buffers: local hit=1594986 read=383