explain.depesz.com

PostgreSQL's explain analyze made readable

Result: egHp

Settings
# exclusive inclusive rows x rows loops node
1. 1.743 73,944.233 ↓ 285.0 285 1

Nested Loop (cost=3,158.16..3,166.20 rows=1 width=45) (actual time=68,445.527..73,944.233 rows=285 loops=1)

  • Buffers: shared hit=440 read=1234311
2. 0.721 68,421.470 ↓ 285.0 285 1

Unique (cost=3,157.73..3,157.73 rows=1 width=16) (actual time=68,420.467..68,421.470 rows=285 loops=1)

  • Buffers: shared hit=8 read=1233603
3. 11.239 68,420.749 ↓ 285.0 285 1

Sort (cost=3,157.73..3,157.73 rows=1 width=16) (actual time=68,420.465..68,420.749 rows=285 loops=1)

  • Sort Key: t.entity_id
  • Sort Method: quicksort Memory: 38kB
  • Buffers: shared hit=8 read=1233603
4. 0.098 68,409.510 ↓ 285.0 285 1

Subquery Scan on t (cost=0.70..3,157.72 rows=1 width=16) (actual time=68,226.887..68,409.510 rows=285 loops=1)

  • Buffers: shared hit=5 read=1233603
5. 2,119.558 68,409.412 ↓ 285.0 285 1

HashSetOp Intersect (cost=0.70..3,157.71 rows=1 width=20) (actual time=68,226.886..68,409.412 rows=285 loops=1)

  • Buffers: shared hit=5 read=1233603
6. 189.440 66,289.854 ↓ 7,539.3 3,000,650 1

Append (cost=0.70..3,156.71 rows=398 width=20) (actual time=36.598..66,289.854 rows=3,000,650 loops=1)

  • Buffers: shared hit=5 read=1233603
7. 368.243 55,896.367 ↓ 3,000,000.0 3,000,000 1

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

  • Buffers: shared read=1231257
8. 55,528.124 55,528.124 ↓ 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=36.586..55,528.124 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.036 10,204.047 ↓ 1.6 650 1

Result (cost=0.70..3,146.18 rows=397 width=20) (actual time=10,203.960..10,204.047 rows=650 loops=1)

  • Buffers: shared hit=5 read=2346
10. 5.952 10,204.011 ↓ 1.6 650 1

HashSetOp Intersect (cost=0.70..3,142.21 rows=397 width=20) (actual time=10,203.959..10,204.011 rows=650 loops=1)

  • Buffers: shared hit=5 read=2346
11. 1.324 10,198.059 ↓ 2.9 2,379 1

Append (cost=0.70..3,140.19 rows=807 width=20) (actual time=41.247..10,198.059 rows=2,379 loops=1)

  • Buffers: shared hit=5 read=2346
12. 1.221 4,926.793 ↓ 2.2 891 1

Subquery Scan on *SELECT* 1 (cost=0.70..1,543.09 rows=397 width=20) (actual time=41.246..4,926.793 rows=891 loops=1)

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

  • Index Cond: ((detail_type = 'Tag'::text) AND (name = 'Tag1'::text) AND (value = 'tag 2494'::text))
  • Buffers: shared read=900
14. 1.564 5,269.942 ↓ 3.6 1,488 1

Subquery Scan on *SELECT* 2 (cost=0.70..1,593.07 rows=410 width=20) (actual time=30.134..5,269.942 rows=1,488 loops=1)

  • Buffers: shared hit=5 read=1446
15. 5,268.378 5,268.378 ↓ 3.6 1,488 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail entity_detail_2 (cost=0.70..1,588.97 rows=410 width=16) (actual time=30.132..5,268.378 rows=1,488 loops=1)

  • Index Cond: ((detail_type = 'Tag'::text) AND (name = 'Tag2'::text) AND (value = 'tag 6289'::text))
  • Buffers: shared hit=5 read=1446
16. 5,521.020 5,521.020 ↑ 1.0 1 285

Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=45) (actual time=19.372..19.372 rows=1 loops=285)

  • Index Cond: (id = t.entity_id)
  • Buffers: shared hit=432 read=708