explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3KZG

Settings
# exclusive inclusive rows x rows loops node
1. 4,069.586 54,944.421 ↓ 0.0 0 1

Insert on pg_temp_13.tmp_scopeaccbook (cost=965,842.83..992,765.83 rows=538,460 width=40) (actual time=54,944.421..54,944.421 rows=0 loops=1)

  • Buffers: local hit=5577083 read=194391 dirtied=45696 written=41603, temp read=145540 written=145496
2. 10,953.669 50,874.835 ↓ 10.2 5,483,422 1

HashAggregate (cost=965,842.83..987,381.23 rows=538,460 width=40) (actual time=46,995.812..50,874.835 rows=5,483,422 loops=1)

  • Output: kwli.idacc, alp.positionmode, (((alp.linestart - tsaop.info1) + 1)), (CASE WHEN (alp.linestart = tsaop.info1) THEN ((alp.columnstart - tsaop.info2) + 1) ELSE alp.columnstart END), (CASE WHEN (alp.lineend = '-1'::integer) THEN alp.lineend ELSE ((alp.lineend - tsaop.info1) + 1) END), (CASE WHEN ((alp.lineend = tsaop.info1) AND (alp.columnend <> '-1'::integer)) THEN ((alp.columnend - tsaop.info2) + 1) ELSE alp.columnend END), tsaop.prop, tsaop.idobjref, kwli.isprototype, (0)
  • Group Key: kwli.idacc, alp.positionmode, ((alp.linestart - tsaop.info1) + 1), CASE WHEN (alp.linestart = tsaop.info1) THEN ((alp.columnstart - tsaop.info2) + 1) ELSE alp.columnstart END, CASE WHEN (alp.lineend = '-1'::integer) THEN alp.lineend ELSE ((alp.lineend - tsaop.info1) + 1) END, CASE WHEN ((alp.lineend = tsaop.info1) AND (alp.columnend <> '-1'::integer)) THEN ((alp.columnend - tsaop.info2) + 1) ELSE alp.columnend END, tsaop.prop, tsaop.idobjref, kwli.isprototype, 0
  • Buffers: local hit=2285 read=148682, temp read=145540 written=145496
3. 12,878.225 39,921.166 ↓ 12.6 6,777,548 1

Hash Join (cost=774,868.34..952,381.33 rows=538,460 width=40) (actual time=24,983.389..39,921.166 rows=6,777,548 loops=1)

  • Output: kwli.idacc, alp.positionmode, ((alp.linestart - tsaop.info1) + 1), CASE WHEN (alp.linestart = tsaop.info1) THEN ((alp.columnstart - tsaop.info2) + 1) ELSE alp.columnstart END, CASE WHEN (alp.lineend = '-1'::integer) THEN alp.lineend ELSE ((alp.lineend - tsaop.info1) + 1) END, CASE WHEN ((alp.lineend = tsaop.info1) AND (alp.columnend <> '-1'::integer)) THEN ((alp.columnend - tsaop.info2) + 1) ELSE alp.columnend END, tsaop.prop, tsaop.idobjref, kwli.isprototype, 0
  • Hash Cond: (kwli.linkid = al.linkid)
  • Buffers: local hit=2285 read=148682, temp read=145540 written=145496
4. 2,067.521 2,067.521 ↓ 1.0 7,189,031 1

Seq Scan on pg_temp_13.kb_work_linkids kwli (cost=0.00..128,722.64 rows=7,189,011 width=12) (actual time=0.114..2,067.521 rows=7,189,031 loops=1)

  • Output: kwli.linkid, kwli.linkkind, kwli.isprototype, kwli.idacc
  • Filter: ('S'::bpchar = kwli.linkkind)
  • Buffers: local read=38860
5. 3,573.082 24,975.420 ↓ 11.9 6,777,948 1

Hash (cost=767,771.14..767,771.14 rows=567,776 width=44) (actual time=24,975.420..24,975.420 rows=6,777,948 loops=1)

  • Output: al.linkid, alp.positionmode, alp.linestart, alp.columnstart, alp.lineend, alp.columnend, alp.linkid, tsaop.info1, tsaop.info2, tsaop.prop, tsaop.idobjref
  • Buckets: 1048576 (originally 1048576) Batches: 16 (originally 1) Memory Usage: 57345kB
  • Buffers: local hit=2285 read=109822, temp read=58062 written=107680
6. 5,526.792 21,402.338 ↓ 11.9 6,777,948 1

Hash Join (cost=271,125.89..767,771.14 rows=567,776 width=44) (actual time=4,794.423..21,402.338 rows=6,777,948 loops=1)

  • Output: al.linkid, alp.positionmode, alp.linestart, alp.columnstart, alp.lineend, alp.columnend, alp.linkid, tsaop.info1, tsaop.info2, tsaop.prop, tsaop.idobjref
  • Hash Cond: ((al.callerid = kwoi.objectid) AND (al.callerkind = kwoi.objectkind) AND (alp.sourceid = kwois.objectid) AND (alp.sourcekind = kwois.objectkind))
  • Join Filter: ((tsaop.info1 <= alp.linestart) AND (tsaop.info3 >= alp.lineend) AND ((tsaop.info1 <> alp.linestart) OR (tsaop.info2 <= alp.columnstart)) AND ((tsaop.info3 <> alp.lineend) OR (tsaop.info4 >= alp.columnend)))
  • Rows Removed by Join Filter: 150570
  • Buffers: local hit=2285 read=109822, temp read=58062 written=58048
7. 8,960.623 14,910.681 ↑ 1.0 6,777,787 1

Hash Join (cost=236,822.86..631,639.55 rows=6,788,534 width=40) (actual time=3,827.766..14,910.681 rows=6,777,787 loops=1)

  • Output: al.linkid, al.callerid, al.callerkind, alp.positionmode, alp.linestart, alp.columnstart, alp.lineend, alp.columnend, alp.linkid, alp.sourceid, alp.sourcekind
  • Hash Cond: (alp.linkid = al.linkid)
  • Buffers: local hit=2 read=106701, temp read=58062 written=58048
8. 2,141.316 2,141.316 ↑ 1.0 6,777,787 1

Seq Scan on pg_temp_13.amt_linkpositions alp (cost=0.00..124,457.34 rows=6,788,534 width=30) (actual time=0.035..2,141.316 rows=6,777,787 loops=1)

  • Output: alp.positionmode, alp.linestart, alp.columnstart, alp.lineend, alp.columnend, alp.linkid, alp.sourceid, alp.sourcekind
  • Buffers: local hit=1 read=56571
9. 2,046.114 3,808.742 ↑ 1.0 6,817,750 1

Hash (cost=118,309.16..118,309.16 rows=6,817,816 width=10) (actual time=3,808.742..3,808.742 rows=6,817,750 loops=1)

  • Output: al.linkid, al.callerid, al.callerkind
  • Buckets: 2097152 Batches: 8 Memory Usage: 51349kB
  • Buffers: local hit=1 read=50130, temp written=21842
10. 1,762.628 1,762.628 ↑ 1.0 6,817,750 1

Seq Scan on pg_temp_13.amt_links al (cost=0.00..118,309.16 rows=6,817,816 width=10) (actual time=0.035..1,762.628 rows=6,817,750 loops=1)

  • Output: al.linkid, al.callerid, al.callerkind
  • Buffers: local hit=1 read=50130
11. 188.964 964.865 ↓ 1.9 330,382 1

Hash (cost=30,779.53..30,779.53 rows=176,175 width=36) (actual time=964.865..964.865 rows=330,382 loops=1)

  • Output: kwoi.objectid, kwoi.objectkind, tsaop.info1, tsaop.info2, tsaop.prop, tsaop.idobjref, tsaop.info3, tsaop.info4, kwois.objectid, kwois.objectkind
  • Buckets: 524288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 26681kB
  • Buffers: local hit=2283 read=3121
12. 187.788 775.901 ↓ 1.9 330,382 1

Hash Join (cost=17,558.81..30,779.53 rows=176,175 width=36) (actual time=402.187..775.901 rows=330,382 loops=1)

  • Output: kwoi.objectid, kwoi.objectkind, tsaop.info1, tsaop.info2, tsaop.prop, tsaop.idobjref, tsaop.info3, tsaop.info4, kwois.objectid, kwois.objectkind
  • Hash Cond: (tsaop.idobj = kwoi.idkey)
  • Buffers: local hit=2283 read=3121
13. 119.965 479.113 ↓ 1.9 330,382 1

Hash Join (cost=10,821.28..21,619.59 rows=176,175 width=34) (actual time=291.643..479.113 rows=330,382 loops=1)

  • Output: tsaop.info1, tsaop.info2, tsaop.prop, tsaop.idobjref, tsaop.idobj, tsaop.info3, tsaop.info4, kwois.objectid, kwois.objectkind
  • Hash Cond: (tsaop.idobjref = kwois.idkey)
  • Buffers: local hit=1487 read=2430
14. 69.133 69.133 ↑ 1.1 330,382 1

Seq Scan on pg_temp_13.tmp_scopeaccobjpos tsaop (cost=0.00..5,953.50 rows=352,350 width=28) (actual time=0.039..69.133 rows=330,382 loops=1)

  • Output: tsaop.idobj, tsaop.idobjref, tsaop.info1, tsaop.info2, tsaop.info3, tsaop.info4, tsaop.prop
  • Buffers: local read=2430
15. 66.385 290.015 ↑ 1.0 233,357 1

Hash (cost=7,904.32..7,904.32 rows=233,357 width=10) (actual time=290.015..290.015 rows=233,357 loops=1)

  • Output: kwois.objectid, kwois.objectkind, kwois.idkey
  • Buckets: 262144 Batches: 1 Memory Usage: 12076kB
  • Buffers: local hit=1487
16. 190.229 223.630 ↑ 1.0 233,357 1

HashAggregate (cost=5,570.75..7,904.32 rows=233,357 width=10) (actual time=134.684..223.630 rows=233,357 loops=1)

  • Output: kwois.objectid, kwois.objectkind, kwois.idkey
  • Group Key: kwois.objectid, kwois.objectkind, kwois.idkey
  • Buffers: local hit=1487
17. 33.401 33.401 ↑ 1.0 233,357 1

Seq Scan on pg_temp_13.kb_work_objectids kwois (cost=0.00..3,820.57 rows=233,357 width=10) (actual time=0.019..33.401 rows=233,357 loops=1)

  • Output: kwois.objectid, kwois.objectkind, kwois.idkey
  • Buffers: local hit=1487
18. 59.672 109.000 ↑ 1.0 233,357 1

Hash (cost=3,820.57..3,820.57 rows=233,357 width=10) (actual time=109.000..109.000 rows=233,357 loops=1)

  • Output: kwoi.objectid, kwoi.objectkind, kwoi.idkey
  • Buckets: 262144 Batches: 1 Memory Usage: 12076kB
  • Buffers: local hit=796 read=691
19. 49.328 49.328 ↑ 1.0 233,357 1

Seq Scan on pg_temp_13.kb_work_objectids kwoi (cost=0.00..3,820.57 rows=233,357 width=10) (actual time=0.052..49.328 rows=233,357 loops=1)

  • Output: kwoi.objectid, kwoi.objectkind, kwoi.idkey
  • Buffers: local hit=796 read=691