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=790,660 read=36,297, local hit=868,652 read=24,628 dirtied=6,027 written=6,028, temp read=24,081 written=24,053
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=790,660 read=36,297, local read=18,600 written=4,096, temp read=24,081 written=24,053
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=382,530 read=29,368, local read=18,600 written=4,096, temp read=11,042 written=11,028
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=382,530 read=29,368, local read=10,339 written=4,096, temp read=1,558 written=1,558
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: 855,662
  • Buffers: shared hit=382,530 read=29,368
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: 12,464kB
  • Buffers: local read=10,339 written=4,096, temp read=1,558 written=1,558
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: 1,096,179
  • Buffers: local read=10,339 written=4,096
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: 16,384 Batches: 8 Memory Usage: 8,435kB
  • Buffers: local read=8,261, temp written=6,255
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=8,261
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: 1,024 Batches: 8 (originally 1) Memory Usage: 9,030kB
  • Buffers: shared hit=408,130 read=6,929, temp written=4,297
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=408,130 read=6,929'Total runtime: 85,975.699 ms'