explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FlDn

Settings
# exclusive inclusive rows x rows loops node
1. 1,541.468 85,968.472 ↓ 0.0 0 1

Insert on pg_temp_4.amt_objectnames (cost=121,618.23..172,858.90 rows=176,715 width=581) (actual time=85,968.472..85,968.472 rows=0 loops=1)

  • Buffers: shared hit=790660 read=36297, local hit=868652 read=24628 dirtied=6027 written=6028, temp read=24081 written=24053
2. 8,470.299 84,427.004 ↓ 4.8 855,662 1

Hash Left Join (cost=121,618.23..172,858.90 rows=176,715 width=581) (actual time=6,094.740..84,427.004 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
  • Hash Cond: (io.object_id = icp.object_id)
  • Buffers: shared hit=790660 read=36297, local read=18600 written=4096, temp read=24081 written=24053
3. 3,728.518 73,094.935 ↓ 4.8 855,662 1

Hash Left Join (cost=79,827.75..129,955.11 rows=176,715 width=65) (actual time=3,220.070..73,094.935 rows=855,662 loops=1)

  • Output: io.object_id, aofn.fullname, acp.propertyvalue
  • Hash Cond: (io.object_id = aofn.objectid)
  • Buffers: shared hit=382530 read=29368, local read=18600 written=4096, temp read=11042 written=11028
4. 2,398.257 67,009.917 ↓ 4.8 855,662 1

Merge Left Join (cost=44,793.36..81,572.52 rows=176,715 width=22) (actual time=863.505..67,009.917 rows=855,662 loops=1)

  • Output: io.object_id, acp.propertyvalue
  • Merge Cond: (io.object_id = acp.objectid)
  • Buffers: shared hit=382530 read=29368, local read=10339 written=4096, temp read=1558 written=1558
5. 63,500.602 63,500.602 ↓ 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=23.806..63,500.602 rows=855,662 loops=1)

  • Output: io.object_id
  • Heap Fetches: 855662
  • Buffers: shared hit=382530 read=29368
6. 609.485 1,111.058 ↑ 1.0 175,247 1

Sort (cost=44,793.36..45,234.65 rows=176,517 width=22) (actual time=839.683..1,111.058 rows=175,247 loops=1)

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

Seq Scan on pg_temp_4.amt_charproperties acp (cost=0.00..29,410.39 rows=176,517 width=22) (actual time=0.132..501.573 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 written=4096
8. 1,219.080 2,356.500 ↑ 1.0 855,662 1

Hash (cost=16,817.62..16,817.62 rows=855,662 width=47) (actual time=2,356.500..2,356.500 rows=855,662 loops=1)

  • Output: aofn.fullname, aofn.objectid
  • Buckets: 16384 Batches: 8 Memory Usage: 8435kB
  • Buffers: local read=8261, temp written=6255
9. 1,137.420 1,137.420 ↑ 1.0 855,662 1

Seq Scan on pg_temp_4.amt_objectfullnames aofn (cost=0.00..16,817.62 rows=855,662 width=47) (actual time=0.058..1,137.420 rows=855,662 loops=1)

  • Output: aofn.fullname, aofn.objectid
  • Buffers: local read=8261
10. 1,301.983 2,861.770 ↓ 855,662.0 855,662 1

Hash (cost=41,790.47..41,790.47 rows=1 width=520) (actual time=2,861.770..2,861.770 rows=855,662 loops=1)

  • Output: icp.property_char, icp.object_id
  • Buckets: 1024 Batches: 8 (originally 1) Memory Usage: 9030kB
  • Buffers: shared hit=408130 read=6929, temp written=4297
11. 1,559.787 1,559.787 ↓ 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.078..1,559.787 rows=855,662 loops=1)

  • Output: icp.property_char, icp.object_id
  • Index Cond: ((icp.property_type_id = 3) AND (icp.session_id = 252))
  • Filter: (icp.char_block = 0)
  • Buffers: shared hit=408130 read=6929