explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ihx5

Settings
# exclusive inclusive rows x rows loops node
1. 90.964 67,909.659 ↓ 1,496.8 100,285 1

HashAggregate (cost=1,802.16..1,802.83 rows=67 width=16) (actual time=67,892.899..67,909.659 rows=100,285 loops=1)

  • Group Key: "*SELECT* 2".entity_id
  • Buffers: shared hit=2 read=1333094
2. 10.304 67,818.695 ↓ 1,496.8 100,285 1

Append (cost=0.70..1,801.99 rows=67 width=16) (actual time=62,159.807..67,818.695 rows=100,285 loops=1)

  • Buffers: shared hit=2 read=1333094
3. 0.063 62,305.317 ↓ 285.0 285 1

Result (cost=0.70..1,543.07 rows=1 width=16) (actual time=62,159.806..62,305.317 rows=285 loops=1)

  • Buffers: shared read=1232157
4. 1,963.646 62,305.254 ↓ 285.0 285 1

HashSetOp Intersect (cost=0.70..1,543.06 rows=1 width=20) (actual time=62,159.805..62,305.254 rows=285 loops=1)

  • Buffers: shared read=1232157
5. 208.016 60,341.608 ↓ 7,597.2 3,000,891 1

Append (cost=0.70..1,542.08 rows=395 width=20) (actual time=30.558..60,341.608 rows=3,000,891 loops=1)

  • Buffers: shared read=1232157
6. 367.114 55,126.109 ↓ 3,000,000.0 3,000,000 1

Subquery Scan on *SELECT* 2 (cost=0.70..8.54 rows=1 width=20) (actual time=30.558..55,126.109 rows=3,000,000 loops=1)

  • Buffers: shared read=1231257
7. 54,758.995 54,758.995 ↓ 3,000,000.0 3,000,000 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail (cost=0.70..8.53 rows=1 width=16) (actual time=30.550..54,758.995 rows=3,000,000 loops=1)

  • Index Cond: ((detail_type = 'BacNetProperty'::text) AND (name = 'BACnet Property 6'::text) AND (value = 'BACnet Property 6 value'::text))
  • Buffers: shared read=1231257
8. 0.795 5,007.483 ↓ 2.3 891 1

Subquery Scan on *SELECT* 1 (cost=0.70..1,531.56 rows=394 width=20) (actual time=38.120..5,007.483 rows=891 loops=1)

  • Buffers: shared read=900
9. 5,006.688 5,006.688 ↓ 2.3 891 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail entity_detail_1 (cost=0.70..1,527.62 rows=394 width=16) (actual time=38.118..5,006.688 rows=891 loops=1)

  • Index Cond: ((detail_type = 'Tag'::text) AND (name = 'Tag1'::text) AND (value = 'tag 2494'::text))
  • Buffers: shared read=900
10. 5,503.074 5,503.074 ↓ 1,515.2 100,000 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail entity_detail_2 (cost=0.70..257.92 rows=66 width=16) (actual time=57.106..5,503.074 rows=100,000 loops=1)

  • Index Cond: ((detail_type = 'Parameter'::text) AND (name = 'Device Parameter 2'::text) AND (value = 'Device Parameter 2 value'::text))
  • Buffers: shared hit=2 read=100937