explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jVzh

Settings
# exclusive inclusive rows x rows loops node
1. 1.789 73,688.052 ↓ 285.0 285 1

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

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

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

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

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

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

Subquery Scan on temp (cost=0.70..3,157.72 rows=1 width=16) (actual time=67,862.674..68,042.423 rows=285 loops=1)

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

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

  • Buffers: shared hit=5 read=1233603
6. 205.241 65,910.009 ↓ 7,539.3 3,000,650 1

Append (cost=0.70..3,156.71 rows=398 width=20) (actual time=38.777..65,910.009 rows=3,000,650 loops=1)

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

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

  • Buffers: shared read=1231257
8. 55,178.286 55,178.286 ↓ 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=38.767..55,178.286 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.098 10,162.649 ↓ 1.6 650 1

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

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

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

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

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

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

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

  • Buffers: shared read=900
13. 4,950.596 4,950.596 ↓ 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.395..4,950.596 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.299 5,203.331 ↓ 3.6 1,488 1

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

  • Buffers: shared hit=5 read=1446
15. 5,202.032 5,202.032 ↓ 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.174..5,202.032 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,632.170 5,632.170 ↑ 1.0 1 285

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

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