explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IRU6

Settings
# exclusive inclusive rows x rows loops node
1. 147.243 502,716.460 ↓ 0.0 0 1

Insert on pg_temp_8.tmp_wrongobjectpropertypositions (cost=120,593.57..6,953,393.96 rows=52,298,343 width=38) (actual time=502,716.460..502,716.460 rows=0 loops=1)

  • Buffers: local hit=77497 read=456652 dirtied=639 written=4179, temp read=642472 written=642452
2. 84,978.041 502,569.217 ↑ 685.5 76,289 1

Hash Join (cost=120,593.57..6,953,393.96 rows=52,298,343 width=38) (actual time=7,879.588..502,569.217 rows=76,289 loops=1)

  • Output: aopp.objectid, aopp.propertyid, aopp.sourceid, aopp.sourcekind, aopp.positionid, aopp.positionindex, aopp.linestart, aopp.columnstart, aopp.lineend, aopp.columnend
  • Hash Cond: ((aopp.objectid = op.objectid) AND (aopp.sourceid = op.sourceid) AND (aopp.sourcekind = op.sourcekind))
  • Buffers: local hit=2 read=455953 written=4179, temp read=642472 written=642452
3. 221,827.619 412,607.176 ↓ 1.0 53,434,645 1

Hash Anti Join (cost=25,899.29..4,861,941.97 rows=52,298,343 width=38) (actual time=2,733.059..412,607.176 rows=53,434,645 loops=1)

  • Output: aopp.objectid, aopp.propertyid, aopp.sourceid, aopp.sourcekind, aopp.positionid, aopp.positionindex, aopp.linestart, aopp.columnstart, aopp.lineend, aopp.columnend
  • Hash Cond: ((aopp.objectid = op.objectid) AND (aopp.sourceid = op.sourceid) AND (aopp.sourcekind = op.sourcekind))
  • Join Filter: ((aopp.linestart <= aopp.lineend) AND (aopp.linestart > 0) AND (aopp.columnstart > 0) AND (aopp.linestart >= op.linestart) AND (aopp.lineend <= op.lineend) AND ((aopp.linestart <> op.linestart) OR (aopp.columnstart >= op.columnstart)) AND ((aopp.lineend <> op.lineend) OR (aopp.columnend <= op.columnend)))
  • Rows Removed by Join Filter: 94383
  • Buffers: local hit=2 read=450895 written=4179, temp read=345913 written=345899
4. 188,094.842 188,094.842 ↑ 1.0 53,500,608 1

Seq Scan on pg_temp_8.amt_objectpropertypositions aopp (cost=0.00..980,845.80 rows=53,500,680 width=38) (actual time=4.687..188,094.842 rows=53,500,608 loops=1)

  • Output: aopp.objectid, aopp.propertyid, aopp.sourceid, aopp.sourcekind, aopp.positionid, aopp.positionindex, aopp.linestart, aopp.columnstart, aopp.lineend, aopp.columnend
  • Buffers: local read=445839 written=92
5. 920.679 2,684.715 ↑ 1.0 606,956 1

Hash (cost=11,127.56..11,127.56 rows=606,956 width=26) (actual time=2,684.715..2,684.715 rows=606,956 loops=1)

  • Output: op.objectid, op.sourceid, op.sourcekind, op.linestart, op.columnstart, op.lineend, op.columnend
  • Buckets: 16384 Batches: 8 Memory Usage: 4464kB
  • Buffers: local hit=2 read=5056 written=4087, temp written=3108
6. 1,764.036 1,764.036 ↑ 1.0 606,956 1

Seq Scan on pg_temp_8.amt_objectpositions op (cost=0.00..11,127.56 rows=606,956 width=26) (actual time=6.344..1,764.036 rows=606,956 loops=1)

  • Output: op.objectid, op.sourceid, op.sourcekind, op.linestart, op.columnstart, op.lineend, op.columnend
  • Buffers: local hit=2 read=5056 written=4087
7. 867.986 4,984.000 ↓ 1.0 596,596 1

Hash (cost=81,575.18..81,575.18 rows=586,120 width=10) (actual time=4,984.000..4,984.000 rows=596,596 loops=1)

  • Output: op.objectid, op.sourceid, op.sourcekind
  • Buckets: 32768 Batches: 4 Memory Usage: 6128kB
  • Buffers: local read=5058, temp read=1485 written=3122
8. 1,482.196 4,116.014 ↓ 1.0 596,596 1

Unique (cost=75,505.62..81,575.18 rows=586,120 width=10) (actual time=1,836.836..4,116.014 rows=596,596 loops=1)

  • Output: op.objectid, op.sourceid, op.sourcekind
  • Buffers: local read=5058, temp read=1485 written=1485
9. 2,633.818 2,633.818 ↑ 1.0 606,956 1

Sort (cost=75,505.62..77,023.01 rows=606,956 width=10) (actual time=1,836.830..2,633.818 rows=606,956 loops=1)

  • Output: op.objectid, op.sourceid, op.sourcekind