explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1iKT

Settings
# exclusive inclusive rows x rows loops node
1. 166,119.159 278,787.949 ↓ 0.0 0 1

Update on pg_temp_4.tmp_scopeobjectpositions (cost=3,611,641.05..8,315,372.62 rows=2,407,529 width=90) (actual time=278,787.949..278,787.949 rows=0 loops=1)

  • Buffers: local hit=107472696 read=2028551 dirtied=1028549 written=1024454, temp read=426777 written=426523
2.          

CTE newmetricpositionids

3. 28.587 22,293.759 ↓ 2.5 33,896 1

WindowAgg (cost=1,303,590.65..1,303,898.05 rows=13,662 width=16) (actual time=22,254.391..22,293.759 rows=33,896 loops=1)

  • Output: tsop.objectid, tsop.propertyid, (row_number() OVER (?) + 0)
  • Buffers: local read=500006
4. 24.649 22,265.172 ↓ 2.5 33,896 1

Sort (cost=1,303,590.65..1,303,624.81 rows=13,662 width=8) (actual time=22,254.360..22,265.172 rows=33,896 loops=1)

  • Output: tsop.objectid, tsop.propertyid
  • Sort Key: tsop.objectid, tsop.propertyid
  • Sort Method: quicksort Memory: 3125kB
  • Buffers: local read=500006
5. 10,233.447 22,240.523 ↓ 2.5 33,896 1

HashAggregate (cost=1,302,515.60..1,302,652.22 rows=13,662 width=8) (actual time=22,234.009..22,240.523 rows=33,896 loops=1)

  • Output: tsop.objectid, tsop.propertyid
  • Group Key: tsop.objectid, tsop.propertyid
  • Buffers: local read=500006
6. 12,007.076 12,007.076 ↑ 1.0 53,500,608 1

Seq Scan on pg_temp_4.tmp_scopeobjectpositions tsop (cost=0.00..1,035,012.40 rows=53,500,640 width=8) (actual time=0.139..12,007.076 rows=53,500,608 loops=1)

  • Output: tsop.metricpositionid, tsop.objectid, tsop.propertyid, tsop.sourceid, tsop.positionid, tsop.positionindex, tsop.linestart, tsop.colstart, tsop.lineend, tsop.colend, tsop.isexisting
  • Filter: (tsop.metricpositionid IS NULL)
  • Buffers: local read=500006
7. 45,221.235 112,668.790 ↓ 22.2 53,500,608 1

Hash Join (cost=2,307,743.00..7,011,474.57 rows=2,407,529 width=90) (actual time=67,517.617..112,668.790 rows=53,500,608 loops=1)

  • Output: nmpi.metricpositionid, tmp_scopeobjectpositions.objectid, tmp_scopeobjectpositions.propertyid, tmp_scopeobjectpositions.sourceid, tmp_scopeobjectpositions.positionid, tmp_scopeobjectpositions.positionindex, tmp_scopeobjectpositions.linestart, tmp_scopeobjectpositions.colstart, tmp_scopeobjectpositions.lineend, tmp_scopeobjectpositions.colend, tmp_scopeobjectpositions.isexisting, tmp_scopeobjectpositions.ctid, nmpi.*
  • Hash Cond: ((nmpi.objectid = tmp_scopeobjectpositions.objectid) AND (nmpi.propertyid = tmp_scopeobjectpositions.propertyid))
  • Buffers: local read=1000012, temp read=426777 written=426523
8. 22,309.715 22,309.715 ↓ 2.5 33,896 1

CTE Scan on newmetricpositionids nmpi (cost=0.00..273.24 rows=13,662 width=56) (actual time=22,254.416..22,309.715 rows=33,896 loops=1)

  • Output: nmpi.metricpositionid, nmpi.*, nmpi.objectid, nmpi.propertyid
  • Buffers: local read=500006
9. 23,407.921 45,137.840 ↑ 1.0 53,500,608 1

Hash (cost=1,035,012.40..1,035,012.40 rows=53,500,640 width=46) (actual time=45,137.840..45,137.840 rows=53,500,608 loops=1)

  • Output: tmp_scopeobjectpositions.objectid, tmp_scopeobjectpositions.propertyid, tmp_scopeobjectpositions.sourceid, tmp_scopeobjectpositions.positionid, tmp_scopeobjectpositions.positionindex, tmp_scopeobjectpositions.linestart, tmp_scopeobjectpositions.colstart, tmp_scopeobjectpositions.lineend, tmp_scopeobjectpositions.colend, tmp_scopeobjectpositions.isexisting, tmp_scopeobjectpositions.ctid
  • Buckets: 1048576 Batches: 128 Memory Usage: 56836kB
  • Buffers: local read=500006, temp written=426015
10. 21,729.919 21,729.919 ↑ 1.0 53,500,608 1

Seq Scan on pg_temp_4.tmp_scopeobjectpositions (cost=0.00..1,035,012.40 rows=53,500,640 width=46) (actual time=0.644..21,729.919 rows=53,500,608 loops=1)

  • Output: tmp_scopeobjectpositions.objectid, tmp_scopeobjectpositions.propertyid, tmp_scopeobjectpositions.sourceid, tmp_scopeobjectpositions.positionid, tmp_scopeobjectpositions.positionindex, tmp_scopeobjectpositions.linestart, tmp_scopeobjectpositions.colstart, tmp_scopeobjectpositions.lineend, tmp_scopeobjectpositions.colend, tmp_scopeobjectpositions.isexisting, tmp_scopeobjectpositions.ctid
  • Filter: (tmp_scopeobjectpositions.metricpositionid IS NULL)
  • Buffers: local read=500006
Planning time : 0.281 ms
Execution time : 278,793.496 ms