explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r2lu

Settings
# exclusive inclusive rows x rows loops node
1. 550,396.484 862,468.027 ↓ 0.0 0 1

Update on pg_temp_44.tmp_objectguids (cost=263,185.82..508,912.48 rows=11,958,960 width=1,450) (actual time=862,468.027..862,468.027 rows=0 loops=1)

  • Buffers: shared hit=724,376 read=28,152, local hit=631,561,817 read=38,570,325 dirtied=26,635 written=29,309, temp read=19,176,220 written=6,959
2. 111,270.406 312,071.543 ↓ 56.0 669,839,016 1

Nested Loop (cost=263,185.82..508,912.48 rows=11,958,960 width=1,450) (actual time=3,570.335..312,071.543 rows=669,839,016 loops=1)

  • Output: tmp_objectguids.objectid, tmp_objectguids.guidlevel, o.idnam, o.idshortnam, tmp_objectguids.newguid, tmp_objectguids.newshortguid, tmp_objectguids.ctid, ao.ctid, aopg.ctid, o.ctid
  • Buffers: shared hit=724,376 read=28,152, local hit=2 read=21,281 written=13,691, temp read=19,176,220 written=6,959
3. 40.379 5,109.025 ↑ 20.7 2,968 1

Hash Join (cost=263,185.82..345,949.95 rows=61,328 width=404) (actual time=3,570.304..5,109.025 rows=2,968 loops=1)

  • Output: ao.ctid, aopg.ctid, o.idnam, o.idshortnam, o.ctid
  • Hash Cond: (aopg.objectid = ao.objectid)
  • Buffers: shared hit=724,376 read=28,152, local hit=2 read=8,293 written=2,674, temp read=499 written=497
4. 156.267 4,911.811 ↑ 20.7 2,968 1

Merge Join (cost=249,935.33..324,283.20 rows=61,328 width=402) (actual time=3,411.814..4,911.811 rows=2,968 loops=1)

  • Output: aopg.ctid, aopg.objectid, o.idnam, o.idshortnam, o.ctid
  • Merge Cond: ((o.idnam)::text = (aopg.guid)::text)
  • Join Filter: ((aopg.shortguid IS NULL) OR ((o.idshortnam)::text = (aopg.shortguid)::text))
  • Buffers: shared hit=724,376 read=28,152, local read=1,227 written=138
5. 4,695.504 4,695.504 ↑ 2.0 944,870 1

Index Scan using idx_objects_idnam on castoncast_local.objects o (cost=0.68..604,593.12 rows=1,898,911 width=392) (actual time=0.057..4,695.504 rows=944,870 loops=1)

  • Output: o.idnam, o.idshortnam, o.ctid
  • Buffers: shared hit=724,376 read=28,152
6. 5.247 60.040 ↑ 2.7 22,363 1

Materialize (cost=24,478.98..24,785.62 rows=61,328 width=644) (actual time=43.015..60.040 rows=22,363 loops=1)

  • Output: aopg.ctid, aopg.objectid, aopg.guid, aopg.shortguid
  • Buffers: local read=1,227 written=138
7. 39.536 54.793 ↑ 2.7 22,363 1

Sort (cost=24,478.98..24,632.30 rows=61,328 width=644) (actual time=43.010..54.793 rows=22,363 loops=1)

  • Output: aopg.ctid, aopg.objectid, aopg.guid, aopg.shortguid
  • Sort Key: aopg.guid
  • Sort Method: quicksort Memory: 9,063kB
  • Buffers: local read=1,227 written=138
8. 15.257 15.257 ↑ 2.7 22,363 1

Seq Scan on pg_temp_44.amt_objectpreviousguids aopg (cost=0.00..1,993.60 rows=61,328 width=644) (actual time=4.035..15.257 rows=22,363 loops=1)

  • Output: aopg.ctid, aopg.objectid, aopg.guid, aopg.shortguid
  • Filter: (aopg.guidlevel = 0)
  • Buffers: local read=1,227 written=138
9. 57.830 156.835 ↑ 1.0 225,687 1

Hash (cost=9,325.55..9,325.55 rows=225,755 width=10) (actual time=156.835..156.835 rows=225,687 loops=1)

  • Output: ao.ctid, ao.objectid
  • Buckets: 262,144 Batches: 2 Memory Usage: 6,892kB
  • Buffers: local hit=2 read=7,066 written=2,536, temp written=441
10. 99.005 99.005 ↑ 1.0 225,687 1

Seq Scan on pg_temp_44.amt_objects ao (cost=0.00..9,325.55 rows=225,755 width=10) (actual time=0.039..99.005 rows=225,687 loops=1)

  • Output: ao.ctid, ao.objectid
  • Buffers: local hit=2 read=7,066 written=2,536
11. 195,378.693 195,692.112 ↓ 1,157.4 225,687 2,968

Materialize (cost=0.00..13,476.02 rows=195 width=1,046) (actual time=0.009..65.934 rows=225,687 loops=2,968)

  • Output: tmp_objectguids.objectid, tmp_objectguids.guidlevel, tmp_objectguids.newguid, tmp_objectguids.newshortguid, tmp_objectguids.ctid
  • Buffers: local read=12,988 written=11,017, temp read=19,175,721 written=6,462
12. 313.419 313.419 ↓ 1,157.4 225,687 1

Seq Scan on pg_temp_44.tmp_objectguids (cost=0.00..13,475.05 rows=195 width=1,046) (actual time=0.026..313.419 rows=225,687 loops=1)

  • Output: tmp_objectguids.objectid, tmp_objectguids.guidlevel, tmp_objectguids.newguid, tmp_objectguids.newshortguid, tmp_objectguids.ctid
  • Filter: ((tmp_objectguids.oldguid)::text = (tmp_objectguids.newguid)::text)
  • Buffers: local read=12,988 written=11,017