explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0QHo

Settings
# exclusive inclusive rows x rows loops node
1. 1.728 22,933.599 ↓ 1.4 285 1

Nested Loop (cost=1,019,706.37..1,021,400.42 rows=200 width=45) (actual time=17,343.212..22,933.599 rows=285 loops=1)

  • Buffers: shared hit=1014 read=197835
2. 0.708 17,316.801 ↓ 1.4 285 1

Unique (cost=1,019,705.94..1,019,707.92 rows=200 width=16) (actual time=17,315.830..17,316.801 rows=285 loops=1)

  • Buffers: shared hit=582 read=197127
3. 0.366 17,316.093 ↑ 1.4 285 1

Sort (cost=1,019,705.94..1,019,706.93 rows=397 width=16) (actual time=17,315.829..17,316.093 rows=285 loops=1)

  • Sort Key: t.entity_id
  • Sort Method: quicksort Memory: 38kB
  • Buffers: shared hit=582 read=197127
4. 0.015 17,315.727 ↑ 1.4 285 1

Subquery Scan on t (cost=0.70..1,019,688.80 rows=397 width=16) (actual time=17,315.697..17,315.727 rows=285 loops=1)

  • Buffers: shared hit=579 read=197127
5. 1.691 17,315.712 ↑ 1.4 285 1

HashSetOp Intersect (cost=0.70..1,019,684.83 rows=397 width=20) (actual time=17,315.697..17,315.712 rows=285 loops=1)

  • Buffers: shared hit=579 read=197127
6. 0.614 17,314.021 ↓ 2.2 1,773 1

Append (cost=0.70..1,019,682.81 rows=807 width=20) (actual time=13,352.647..17,314.021 rows=1,773 loops=1)

  • Buffers: shared hit=579 read=197127
7. 0.022 13,352.711 ↑ 1.4 285 1

Result (cost=0.70..1,018,085.71 rows=397 width=20) (actual time=13,352.646..13,352.711 rows=285 loops=1)

  • Buffers: shared hit=287 read=195968
8. 345.996 13,352.689 ↑ 1.4 285 1

HashSetOp Intersect (cost=0.70..1,018,081.74 rows=397 width=20) (actual time=13,352.644..13,352.689 rows=285 loops=1)

  • Buffers: shared hit=287 read=195968
9. 134.779 13,006.693 ↓ 7.3 3,000,891 1

Append (cost=0.70..1,017,051.68 rows=412,026 width=20) (actual time=50.735..13,006.693 rows=3,000,891 loops=1)

  • Buffers: shared hit=287 read=195968
10. 4,944.878 4,944.878 ↓ 2.2 891 1

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

  • Buffers: shared read=900
  • -> Index Scan using entity_detail_detail_type_name_value_idx on entity_detail (cost=0.70..1539.12 rows=397 width=16) (actual time=50.719..4943.720 rows=891 loops=1) Index Cond: ((detail_type = 'Tag'::text) AND (name = 'Tag1'::text) AND (value = 'tag 2494'::text))
  • Buffers: shared read=900
11. 234.176 7,927.036 ↓ 7.3 3,000,000 1

Subquery Scan on *SELECT* 1 (cost=20,328.96..1,013,448.45 rows=411,629 width=20) (actual time=1,059.531..7,927.036 rows=3,000,000 loops=1)

  • Buffers: shared hit=287 read=195068
12. 6,692.709 7,692.860 ↓ 7.3 3,000,000 1

Bitmap Heap Scan on entity_detail entity_detail_1 (cost=20,328.96..1,009,332.16 rows=411,629 width=16) (actual time=1,059.529..7,692.860 rows=3,000,000 loops=1)

  • Recheck Cond: ((detail_type = 'EntityProperty'::text) AND (name = 'EntityType'::text) AND (value = 'BACnet'::text))
  • Heap Blocks: exact=173922
  • Buffers: shared hit=287 read=195068
13. 1,000.151 1,000.151 ↓ 7.3 3,000,000 1

Bitmap Index Scan on entity_detail_detail_type_name_value_idx (cost=0.00..20,226.06 rows=411,629 width=0) (actual time=1,000.151..1,000.151 rows=3,000,000 loops=1)

  • Index Cond: ((detail_type = 'EntityProperty'::text) AND (name = 'EntityType'::text) AND (value = 'BACnet'::text))
  • Buffers: shared hit=2 read=21431
14. 1.147 3,960.696 ↓ 3.6 1,488 1

Subquery Scan on *SELECT* 3 (cost=0.70..1,593.07 rows=410 width=20) (actual time=45.040..3,960.696 rows=1,488 loops=1)

  • Buffers: shared hit=292 read=1159
15. 3,959.549 3,959.549 ↓ 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=45.037..3,959.549 rows=1,488 loops=1)

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

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

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