explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hdpRS

Settings
# exclusive inclusive rows x rows loops node
1. 1,530.350 18,035.204 ↓ 0.0 0 1

Insert on pg_temp_4.amt_objectnames (cost=44,479.46..151,929.66 rows=176,715 width=581) (actual time=18,035.204..18,035.204 rows=0 loops=1)

  • Buffers: shared hit=787576 read=39381 written=2, local hit=869672 read=26969 dirtied=6027 written=3542, temp read=1558 written=1558
2. 2,430.685 16,504.854 ↓ 4.8 855,662 1

Merge Left Join (cost=44,479.46..151,929.66 rows=176,715 width=581) (actual time=768.339..16,504.854 rows=855,662 loops=1)

  • Output: io.object_id, CASE WHEN (icp.property_char IS NOT NULL) THEN icp.property_char WHEN (aofn.fullname IS NOT NULL) THEN aofn.fullname WHEN (acp.propertyvalue IS NOT NULL) THEN acp.propertyvalue ELSE '[no name]'::character varying END
  • Merge Cond: (io.object_id = acp.objectid)
  • Buffers: shared hit=787576 read=39381 written=2, local hit=1110 read=20941 written=2354, temp read=1558 written=1558
3. 3,193.588 13,054.084 ↓ 4.8 855,662 1

Merge Left Join (cost=0.00..105,249.43 rows=176,715 width=563) (actual time=0.254..13,054.084 rows=855,662 loops=1)

  • Output: io.object_id, icp.property_char, aofn.fullname
  • Merge Cond: (io.object_id = aofn.objectid)
  • Buffers: shared hit=787576 read=39381 written=2, local hit=1110 read=10602 written=2354
4. 3,211.005 8,593.830 ↓ 4.8 855,662 1

Merge Left Join (cost=0.00..77,221.13 rows=176,715 width=520) (actual time=0.150..8,593.830 rows=855,662 loops=1)

  • Output: io.object_id, icp.property_char
  • Merge Cond: (io.object_id = icp.object_id)
  • Buffers: shared hit=787576 read=39381 written=2
5. 1,395.266 1,395.266 ↓ 4.8 855,662 1

Index Only Scan using idx_inobjects on new_fva_local.in_objects io (cost=0.00..34,975.62 rows=176,715 width=4) (actual time=0.088..1,395.266 rows=855,662 loops=1)

  • Output: io.object_id
  • Heap Fetches: 855662
  • Buffers: shared hit=408954 read=2944
6. 2,027.839 3,987.559 ↓ 855,662.0 855,662 1

Materialize (cost=0.00..41,790.47 rows=1 width=520) (actual time=0.053..3,987.559 rows=855,662 loops=1)

  • Output: icp.session_id, icp.object_id, icp.property_type_id, icp.property_offset, icp.char_block, icp.property_char
  • Buffers: shared hit=378622 read=36437 written=2
7. 1,959.720 1,959.720 ↓ 855,662.0 855,662 1

Index Scan using idx_incharprop on new_fva_local.in_char_properties icp (cost=0.00..41,790.47 rows=1 width=520) (actual time=0.043..1,959.720 rows=855,662 loops=1)

  • Output: icp.session_id, icp.object_id, icp.property_type_id, icp.property_offset, icp.char_block, icp.property_char
  • Index Cond: ((icp.property_type_id = 3) AND (icp.session_id = 252))
  • Filter: (icp.char_block = 0)
  • Buffers: shared hit=378622 read=36437 written=2
8. 1,266.666 1,266.666 ↑ 1.0 855,662 1

Index Scan using idx_amt_objectfullnamess_objectid on pg_temp_4.amt_objectfullnames aofn (cost=0.00..23,680.21 rows=855,662 width=47) (actual time=0.097..1,266.666 rows=855,662 loops=1)

  • Output: aofn.objectid, aofn.fullname
  • Buffers: local hit=1110 read=10602 written=2354
9. 592.456 1,020.085 ↓ 1.0 175,247 1

Sort (cost=44,479.46..44,912.43 rows=173,188 width=22) (actual time=768.066..1,020.085 rows=175,247 loops=1)

  • Output: acp.propertyvalue, acp.objectid
  • Sort Key: acp.objectid
  • Sort Method: external sort Disk: 12464kB
  • Buffers: local read=10339, temp read=1558 written=1558
10. 427.629 427.629 ↓ 1.0 175,247 1

Seq Scan on pg_temp_4.amt_charproperties acp (cost=0.00..29,410.39 rows=173,188 width=22) (actual time=0.025..427.629 rows=175,247 loops=1)

  • Output: acp.propertyvalue, acp.objectid
  • Filter: ((acp.propertyid = 103) AND (acp.charblock = 0))
  • Rows Removed by Filter: 1096179
  • Buffers: local read=10339