explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m26J

Settings
# exclusive inclusive rows x rows loops node
1. 260,211.683 362,148.952 ↓ 0.0 0 1

Update on pg_temp_4.tmp_scopeobjectpositions (cost=1,304,809.52..4,169,272.93 rows=2,140,026 width=90) (actual time=362,148.952..362,148.952 rows=0 loops=1)

  • Buffers: local hit=340768188 read=1834840 dirtied=1194043 written=1190140
2.          

CTE newmetricpositionids

3. 25.632 22,804.434 ↓ 2.8 33,896 1

WindowAgg (cost=1,303,464.29..1,303,738.43 rows=12,184 width=16) (actual time=22,771.602..22,804.434 rows=33,896 loops=1)

  • Output: tsop.objectid, tsop.propertyid, (row_number() OVER (?) + 0)
  • Buffers: local hit=1 read=500005
4. 20.452 22,778.802 ↓ 2.8 33,896 1

Sort (cost=1,303,464.29..1,303,494.75 rows=12,184 width=8) (actual time=22,771.581..22,778.802 rows=33,896 loops=1)

  • Output: tsop.objectid, tsop.propertyid
  • Sort Key: tsop.objectid, tsop.propertyid
  • Sort Method: quicksort Memory: 3125kB
  • Buffers: local hit=1 read=500005
5. 10,188.099 22,758.350 ↓ 2.8 33,896 1

HashAggregate (cost=1,302,515.60..1,302,637.44 rows=12,184 width=8) (actual time=22,751.050..22,758.350 rows=33,896 loops=1)

  • Output: tsop.objectid, tsop.propertyid
  • Group Key: tsop.objectid, tsop.propertyid
  • Buffers: local hit=1 read=500005
6. 12,570.251 12,570.251 ↑ 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.057..12,570.251 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 hit=1 read=500005
7. 25,306.787 101,937.269 ↓ 25.0 53,500,608 1

Merge Join (cost=1,071.09..2,865,534.50 rows=2,140,026 width=90) (actual time=22,832.208..101,937.269 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: ((tmp_scopeobjectpositions.objectid = nmpi.objectid) AND (tmp_scopeobjectpositions.propertyid = nmpi.propertyid))
  • Buffers: local hit=30176 read=1143276 written=573734
8. 47,015.763 47,015.763 ↑ 1.0 53,500,608 1

Index Scan using idx_tmp_scopeobjectpositions_objectid_propertyid on pg_temp_4.tmp_scopeobjectpositions (cost=0.56..2,554,160.25 rows=53,500,640 width=46) (actual time=1.923..47,015.763 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 hit=30175 read=643271 written=573734
9. 6,796.467 29,614.719 ↓ 4,391.1 53,500,592 1

Sort (cost=1,070.53..1,100.99 rows=12,184 width=56) (actual time=22,830.275..29,614.719 rows=53,500,592 loops=1)

  • Output: nmpi.metricpositionid, nmpi.*, nmpi.objectid, nmpi.propertyid
  • Sort Key: nmpi.objectid, nmpi.propertyid
  • Sort Method: quicksort Memory: 6303kB
  • Buffers: local hit=1 read=500005
10. 22,818.252 22,818.252 ↓ 2.8 33,896 1

CTE Scan on newmetricpositionids nmpi (cost=0.00..243.68 rows=12,184 width=56) (actual time=22,771.617..22,818.252 rows=33,896 loops=1)

  • Output: nmpi.metricpositionid, nmpi.*, nmpi.objectid, nmpi.propertyid
  • Buffers: local hit=1 read=500005