explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EEu6

Settings
# exclusive inclusive rows x rows loops node
1. 0.052 83,341.168 ↑ 1.0 10 1

Nested Loop (cost=485,808.41..485,892.62 rows=10 width=69) (actual time=83,257.310..83,341.168 rows=10 loops=1)

  • Buffers: shared hit=910 read=1582418
2. 10.140 83,248.146 ↑ 1.0 10 1

Limit (cost=485,807.97..485,808.00 rows=10 width=16) (actual time=83,238.003..83,248.146 rows=10 loops=1)

  • Buffers: shared hit=883 read=1582405
3. 117.830 83,238.006 ↑ 15,375.1 10 1

Sort (cost=485,807.97..486,192.35 rows=153,751 width=16) (actual time=83,238.000..83,238.006 rows=10 loops=1)

  • Sort Key: "*SELECT* 2".entity_id
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=883 read=1582405
4. 726.408 83,120.176 ↓ 6.7 1,024,296 1

HashAggregate (cost=480,947.96..482,485.47 rows=153,751 width=16) (actual time=82,923.509..83,120.176 rows=1,024,296 loops=1)

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

Append (cost=0.70..480,563.58 rows=153,751 width=16) (actual time=62,072.206..82,393.768 rows=1,024,296 loops=1)

  • Buffers: shared hit=880 read=1582405
6. 0.057 62,219.091 ↓ 285.0 285 1

Result (cost=0.70..1,554.63 rows=1 width=16) (actual time=62,072.204..62,219.091 rows=285 loops=1)

  • Buffers: shared read=1232157
7. 1,818.473 62,219.034 ↓ 285.0 285 1

HashSetOp Intersect (cost=0.70..1,554.62 rows=1 width=20) (actual time=62,072.201..62,219.034 rows=285 loops=1)

  • Buffers: shared read=1232157
8. 172.387 60,400.561 ↓ 7,539.9 3,000,891 1

Append (cost=0.70..1,553.62 rows=398 width=20) (actual time=34.841..60,400.561 rows=3,000,891 loops=1)

  • Buffers: shared read=1232157
9. 304.846 54,812.654 ↓ 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.839..54,812.654 rows=3,000,000 loops=1)

  • Buffers: shared read=1231257
10. 54,507.808 54,507.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=34.836..54,507.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
11. 0.821 5,415.520 ↓ 2.2 891 1

Subquery Scan on *SELECT* 1 (cost=0.70..1,543.09 rows=397 width=20) (actual time=54.721..5,415.520 rows=891 loops=1)

  • Buffers: shared read=900
12. 5,414.699 5,414.699 ↓ 2.2 891 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail entity_detail_1 (cost=0.70..1,539.12 rows=397 width=16) (actual time=54.720..5,414.699 rows=891 loops=1)

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

Result (cost=0.70..1,855.88 rows=66 width=16) (actual time=10,227.670..10,231.601 rows=11 loops=1)

  • Buffers: shared hit=6 read=101780
14. 72.833 10,231.595 ↑ 6.0 11 1

HashSetOp Intersect (cost=0.70..1,855.22 rows=66 width=20) (actual time=10,227.669..10,231.595 rows=11 loops=1)

  • Buffers: shared hit=6 read=101780
15. 10.087 10,158.762 ↓ 211.8 100,839 1

Append (cost=0.70..1,854.03 rows=476 width=20) (actual time=30.755..10,158.762 rows=100,839 loops=1)

  • Buffers: shared hit=6 read=101780
16. 19.519 5,417.524 ↓ 1,515.2 100,000 1

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

  • Buffers: shared hit=2 read=100937
17. 5,398.005 5,398.005 ↓ 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.752..5,398.005 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
18. 0.935 4,731.151 ↓ 2.0 839 1

Subquery Scan on *SELECT* 4 (cost=0.70..1,593.07 rows=410 width=20) (actual time=14.403..4,731.151 rows=839 loops=1)

  • Buffers: shared hit=4 read=843
19. 4,730.216 4,730.216 ↓ 2.0 839 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail entity_detail_3 (cost=0.70..1,588.97 rows=410 width=16) (actual time=14.402..4,730.216 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
20. 9,447.156 9,886.738 ↓ 6.7 1,024,000 1

Bitmap Heap Scan on entity_detail entity_detail_4 (cost=7,592.17..474,847.48 rows=153,684 width=16) (actual time=502.102..9,886.738 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
21. 439.582 439.582 ↓ 6.7 1,024,000 1

Bitmap Index Scan on entity_detail_detail_type_name_value_idx (cost=0.00..7,553.74 rows=153,684 width=0) (actual time=439.582..439.582 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
22. 92.970 92.970 ↑ 1.0 1 10

Index Scan using entity_pkey on entity (cost=0.43..8.45 rows=1 width=69) (actual time=9.297..9.297 rows=1 loops=10)

  • Index Cond: (id = "*SELECT* 2".entity_id)
  • Buffers: shared hit=27 read=13