explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uL7g

Settings
# exclusive inclusive rows x rows loops node
1. 704.834 83,645.030 ↓ 6.7 1,024,296 1

HashAggregate (cost=477,215.80..478,741.42 rows=152,562 width=16) (actual time=83,449.632..83,645.030 rows=1,024,296 loops=1)

  • Group Key: "*SELECT* 2".entity_id
  • Buffers: shared hit=880 read=1582405
2. 56.026 82,940.196 ↓ 6.7 1,024,296 1

Append (cost=0.70..476,834.39 rows=152,562 width=16) (actual time=62,423.547..82,940.196 rows=1,024,296 loops=1)

  • Buffers: shared hit=880 read=1582405
3. 0.051 62,578.819 ↓ 285.0 285 1

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

  • Buffers: shared read=1232157
4. 1,938.828 62,578.768 ↓ 285.0 285 1

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

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

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

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

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

  • Buffers: shared read=1231257
7. 55,086.808 55,086.808 ↓ 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=35.999..55,086.808 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.774 5,007.434 ↓ 2.3 891 1

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

  • Buffers: shared read=900
9. 5,006.660 5,006.660 ↓ 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.133..5,006.660 rows=891 loops=1)

  • Index Cond: ((detail_type = 'Tag'::text) AND (name = 'Tag1'::text) AND (value = 'tag 2494'::text))
  • Buffers: shared read=900
10. 0.004 10,088.957 ↑ 6.0 11 1

Result (cost=0.70..1,840.47 rows=66 width=16) (actual time=10,085.813..10,088.957 rows=11 loops=1)

  • Buffers: shared hit=6 read=101780
11. 71.431 10,088.953 ↑ 6.0 11 1

HashSetOp Intersect (cost=0.70..1,839.81 rows=66 width=20) (actual time=10,085.812..10,088.953 rows=11 loops=1)

  • Buffers: shared hit=6 read=101780
12. 10.471 10,017.522 ↓ 213.6 100,839 1

Append (cost=0.70..1,838.63 rows=472 width=20) (actual time=30.667..10,017.522 rows=100,839 loops=1)

  • Buffers: shared hit=6 read=101780
13. 20.083 5,366.985 ↓ 1,515.2 100,000 1

Subquery Scan on *SELECT* 3 (cost=0.70..258.58 rows=66 width=20) (actual time=30.665..5,366.985 rows=100,000 loops=1)

  • Buffers: shared hit=2 read=100937
14. 5,346.902 5,346.902 ↓ 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=30.664..5,346.902 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
15. 0.690 4,640.066 ↓ 2.1 839 1

Subquery Scan on *SELECT* 4 (cost=0.70..1,577.69 rows=406 width=20) (actual time=14.314..4,640.066 rows=839 loops=1)

  • Buffers: shared hit=4 read=843
16. 4,639.376 4,639.376 ↓ 2.1 839 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail entity_detail_3 (cost=0.70..1,573.63 rows=406 width=16) (actual time=14.313..4,639.376 rows=839 loops=1)

  • Index Cond: ((detail_type = 'Tag'::text) AND (name = 'Tag2'::text) AND (value = 'tag 6289'::text))
  • Buffers: shared hit=4 read=843
17. 9,769.129 10,216.394 ↓ 6.7 1,024,000 1

Bitmap Heap Scan on entity_detail entity_detail_4 (cost=7,525.01..471,163.09 rows=152,495 width=16) (actual time=519.648..10,216.394 rows=1,024,000 loops=1)

  • Recheck Cond: ((detail_type = 'EntityProperty'::text) AND (name = 'DisplayName'::text) AND (value = 'I/O Channel 2'::text))
  • Heap Blocks: exact=240943
  • Buffers: shared hit=874 read=248468
18. 447.265 447.265 ↓ 6.7 1,024,000 1

Bitmap Index Scan on entity_detail_detail_type_name_value_idx (cost=0.00..7,486.88 rows=152,495 width=0) (actual time=447.265..447.265 rows=1,024,000 loops=1)

  • Index Cond: ((detail_type = 'EntityProperty'::text) AND (name = 'DisplayName'::text) AND (value = 'I/O Channel 2'::text))
  • Buffers: shared hit=2 read=8397