explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BgI

Settings
# exclusive inclusive rows x rows loops node
1. 408.750 123,260.603 ↓ 1,500,000.0 3,000,000 1

Unique (cost=1,551.63..1,551.64 rows=2 width=16) (actual time=122,452.627..123,260.603 rows=3,000,000 loops=1)

  • Buffers: shared hit=5 read=2463412
2. 1,884.750 122,851.853 ↓ 1,500,142.5 3,000,285 1

Sort (cost=1,551.63..1,551.64 rows=2 width=16) (actual time=122,452.625..122,851.853 rows=3,000,285 loops=1)

  • Sort Key: "*SELECT* 2".entity_id
  • Sort Method: quicksort Memory: 238943kB
  • Buffers: shared hit=5 read=2463412
3. 209.558 120,967.103 ↓ 1,500,142.5 3,000,285 1

Append (cost=0.70..1,551.62 rows=2 width=16) (actual time=63,506.143..120,967.103 rows=3,000,285 loops=1)

  • Buffers: shared hit=2 read=2463412
4. 0.056 63,654.836 ↓ 285.0 285 1

Result (cost=0.70..1,543.07 rows=1 width=16) (actual time=63,506.143..63,654.836 rows=285 loops=1)

  • Buffers: shared read=1232157
5. 1,950.464 63,654.780 ↓ 285.0 285 1

HashSetOp Intersect (cost=0.70..1,543.06 rows=1 width=20) (actual time=63,506.141..63,654.780 rows=285 loops=1)

  • Buffers: shared read=1232157
6. 183.909 61,704.316 ↓ 7,597.2 3,000,891 1

Append (cost=0.70..1,542.08 rows=395 width=20) (actual time=34.822..61,704.316 rows=3,000,891 loops=1)

  • Buffers: shared read=1232157
7. 352.486 56,563.021 ↓ 3,000,000.0 3,000,000 1

Subquery Scan on *SELECT* 2 (cost=0.70..8.54 rows=1 width=20) (actual time=34.822..56,563.021 rows=3,000,000 loops=1)

  • Buffers: shared read=1231257
8. 56,210.535 56,210.535 ↓ 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=34.815..56,210.535 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
9. 0.841 4,957.386 ↓ 2.3 891 1

Subquery Scan on *SELECT* 1 (cost=0.70..1,531.56 rows=394 width=20) (actual time=38.209..4,957.386 rows=891 loops=1)

  • Buffers: shared read=900
10. 4,956.545 4,956.545 ↓ 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.207..4,956.545 rows=891 loops=1)

  • Index Cond: ((detail_type = 'Tag'::text) AND (name = 'Tag1'::text) AND (value = 'tag 2494'::text))
  • Buffers: shared read=900
11. 57,102.709 57,102.709 ↓ 3,000,000.0 3,000,000 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail entity_detail_2 (cost=0.70..8.53 rows=1 width=16) (actual time=24.920..57,102.709 rows=3,000,000 loops=1)

  • Index Cond: ((detail_type = 'BacNetProperty'::text) AND (name = 'BACnet Property 8'::text) AND (value = 'BACnet Property 8 value'::text))
  • Buffers: shared hit=2 read=1231255