explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3oR

Settings
# exclusive inclusive rows x rows loops node
1. 1.695 17,199.752 ↑ 1.4 285 1

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

  • Buffers: shared hit=579 read=197127
2. 0.717 17,198.057 ↓ 2.2 1,773 1

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

  • Buffers: shared hit=579 read=197127
3. 0.018 13,428.819 ↑ 1.4 285 1

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

  • Buffers: shared hit=287 read=195968
4. 344.752 13,428.801 ↑ 1.4 285 1

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

  • Buffers: shared hit=287 read=195968
5. 141.876 13,084.049 ↓ 7.3 3,000,891 1

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

  • Buffers: shared hit=287 read=195968
6. 0.975 4,903.536 ↓ 2.2 891 1

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

  • Buffers: shared read=900
7. 4,902.561 4,902.561 ↓ 2.2 891 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail (cost=0.70..1,539.12 rows=397 width=16) (actual time=43.244..4,902.561 rows=891 loops=1)

  • Index Cond: ((detail_type = 'Tag'::text) AND (name = 'Tag1'::text) AND (value = 'tag 2494'::text))
  • Buffers: shared read=900
8. 243.724 8,038.637 ↓ 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=984.599..8,038.637 rows=3,000,000 loops=1)

  • Buffers: shared hit=287 read=195068
9. 6,878.036 7,794.913 ↓ 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=984.599..7,794.913 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
10. 916.877 916.877 ↓ 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=916.877..916.877 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
11. 0.955 3,768.521 ↓ 3.6 1,488 1

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

  • Buffers: shared hit=292 read=1159
12. 3,767.566 3,767.566 ↓ 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=36.602..3,767.566 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