explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YVJb

Settings
# exclusive inclusive rows x rows loops node
1. 1,542.363 26,027.787 ↓ 0.0 0 1

Insert on pg_temp_4.amt_objectnames (cost=120,957.78..172,127.89 rows=176,715 width=581) (actual time=26,027.787..26,027.787 rows=0 loops=1)

  • Buffers: shared hit=779379 read=47578 written=2, local hit=868652 read=24628 dirtied=6027 written=1932, temp read=24081 written=24053
2. 8,249.425 24,485.424 ↓ 4.8 855,662 1

Hash Left Join (cost=120,957.78..172,127.89 rows=176,715 width=581) (actual time=8,695.881..24,485.424 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=779379 read=47578 written=2, local read=18600, temp read=24081 written=24053
3. 3,570.937 10,614.518 ↓ 4.8 855,662 1

Hash Left Join (cost=79,167.30..129,224.10 rows=176,715 width=65) (actual time=3,070.957..10,614.518 rows=855,662 loops=1)

  • Output: io.object_id, aofn.fullname, acp.propertyvalue
  • Hash Cond: (io.object_id = aofn.objectid)
  • Buffers: shared hit=398592 read=13306, local read=18600, temp read=11042 written=11028
4. 2,266.402 4,723.648 ↓ 4.8 855,662 1

Merge Left Join (cost=44,132.91..80,841.51 rows=176,715 width=22) (actual time=750.965..4,723.648 rows=855,662 loops=1)

  • Output: io.object_id, acp.propertyvalue
  • Merge Cond: (io.object_id = acp.objectid)
  • Buffers: shared hit=398592 read=13306, local read=10339, temp read=1558 written=1558
5. 1,463.326 1,463.326 ↓ 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.105..1,463.326 rows=855,662 loops=1)

  • Output: io.object_id
  • Heap Fetches: 855662
  • Buffers: shared hit=398592 read=13306
6. 572.599 993.920 ↓ 1.0 175,247 1

Sort (cost=44,132.91..44,556.68 rows=169,507 width=22) (actual time=750.848..993.920 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
7. 421.321 421.321 ↓ 1.0 175,247 1

Seq Scan on pg_temp_4.amt_charproperties acp (cost=0.00..29,410.39 rows=169,507 width=22) (actual time=0.031..421.321 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
8. 1,194.897 2,319.933 ↑ 1.0 855,662 1

Hash (cost=16,817.62..16,817.62 rows=855,662 width=47) (actual time=2,319.933..2,319.933 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,125.036 1,125.036 ↑ 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.036..1,125.036 rows=855,662 loops=1)

  • Output: aofn.fullname, aofn.objectid
  • Buffers: local read=8261
10. 1,321.115 5,621.481 ↓ 855,662.0 855,662 1

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

  • Output: icp.property_char, icp.object_id
  • Buckets: 1024 Batches: 8 (originally 1) Memory Usage: 9030kB
  • Buffers: shared hit=380787 read=34272 written=2, temp written=4297
11. 4,300.366 4,300.366 ↓ 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.066..4,300.366 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=380787 read=34272 written=2
Total runtime : 26,034.359 ms