explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nSnO

Settings
# exclusive inclusive rows x rows loops node
1. 106,185.124 257,106.505 ↓ 0.0 0 1

Update on pg_temp_9.tmp_scopeobjectpositions (cost=2,165,223.38..2,171,170.18 rows=282,263 width=90) (actual time=257,106.505..257,106.505 rows=0 loops=1)

  • Buffers: local hit=107500408 read=2000819 dirtied=1000818 written=996723, temp read=784205 written=784205
2.          

CTE newmetricpositionids

3. 28.750 25,057.286 ↑ 1.2 33,896 1

WindowAgg (cost=1,069,879.65..1,070,778.89 rows=39,966 width=16) (actual time=25,020.724..25,057.286 rows=33,896 loops=1)

  • Output: tsop.objectid, tsop.propertyid, (row_number() OVER (?) + 0)
  • Buffers: local read=500006
4. 22.474 25,028.536 ↑ 1.2 33,896 1

Sort (cost=1,069,879.65..1,069,979.57 rows=39,966 width=8) (actual time=25,020.667..25,028.536 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,817.636 25,006.062 ↑ 1.2 33,896 1

HashAggregate (cost=1,066,425.29..1,066,824.95 rows=39,966 width=8) (actual time=24,998.918..25,006.062 rows=33,896 loops=1)

  • Output: tsop.objectid, tsop.propertyid
  • Group Key: tsop.objectid, tsop.propertyid
  • Buffers: local read=500006
6. 14,188.426 14,188.426 ↓ 189.4 53,500,608 1

Seq Scan on pg_temp_9.tmp_scopeobjectpositions tsop (cost=0.00..1,065,012.78 rows=282,503 width=8) (actual time=1.472..14,188.426 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. 18,713.062 150,921.381 ↓ 189.5 53,500,608 1

Merge Join (cost=1,094,444.49..1,100,391.29 rows=282,263 width=90) (actual time=107,925.876..150,921.381 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.*
  • Merge Cond: ((nmpi.objectid = tmp_scopeobjectpositions.objectid) AND (nmpi.propertyid = tmp_scopeobjectpositions.propertyid))
  • Buffers: local read=1000012, temp read=784205 written=784205
8. 22.219 25,093.373 ↑ 1.2 33,896 1

Sort (cost=3,854.02..3,953.93 rows=39,966 width=56) (actual time=25,084.303..25,093.373 rows=33,896 loops=1)

  • Output: nmpi.metricpositionid, nmpi.*, nmpi.objectid, nmpi.propertyid
  • Sort Key: nmpi.objectid, nmpi.propertyid
  • Sort Method: quicksort Memory: 6303kB
  • Buffers: local read=500006
9. 25,071.154 25,071.154 ↑ 1.2 33,896 1

CTE Scan on newmetricpositionids nmpi (cost=0.00..799.32 rows=39,966 width=56) (actual time=25,020.750..25,071.154 rows=33,896 loops=1)

  • Output: nmpi.metricpositionid, nmpi.*, nmpi.objectid, nmpi.propertyid
  • Buffers: local read=500006
10. 84,870.132 107,114.946 ↓ 189.4 53,500,608 1

Sort (cost=1,090,590.47..1,091,296.73 rows=282,503 width=46) (actual time=82,841.547..107,114.946 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
  • Sort Key: tmp_scopeobjectpositions.objectid, tmp_scopeobjectpositions.propertyid
  • Sort Method: external sort Disk: 3136344kB
  • Buffers: local read=500006, temp read=784205 written=784205
11. 22,244.814 22,244.814 ↓ 189.4 53,500,608 1

Seq Scan on pg_temp_9.tmp_scopeobjectpositions (cost=0.00..1,065,012.78 rows=282,503 width=46) (actual time=0.640..22,244.814 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