explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zl99

Settings
# exclusive inclusive rows x rows loops node
1. 1.630 15,853.779 ↓ 10.0 10 1

HashSetOp Intersect (cost=0.70..1,018,895.82 rows=1 width=20) (actual time=15,853.779..15,853.779 rows=10 loops=1)

  • Buffers: shared hit=581 read=197132
2. 0.570 15,852.149 ↓ 3.7 1,498 1

Append (cost=0.70..1,018,894.80 rows=410 width=20) (actual time=11,880.982..15,852.149 rows=1,498 loops=1)

  • Buffers: shared hit=581 read=197132
3. 0.002 11,880.984 ↓ 10.0 10 1

Result (cost=0.70..1,017,303.52 rows=1 width=20) (actual time=11,880.982..11,880.984 rows=10 loops=1)

  • Buffers: shared hit=289 read=195973
4. 1.196 11,880.982 ↓ 10.0 10 1

HashSetOp Intersect (cost=0.70..1,017,303.51 rows=1 width=20) (actual time=11,880.981..11,880.982 rows=10 loops=1)

  • Buffers: shared hit=289 read=195973
5. 0.454 11,879.786 ↓ 2.3 901 1

Append (cost=0.70..1,017,302.52 rows=398 width=20) (actual time=8,515.222..11,879.786 rows=901 loops=1)

  • Buffers: shared hit=289 read=195973
6. 0.004 8,515.224 ↓ 10.0 10 1

Result (cost=0.70..1,015,757.44 rows=1 width=20) (actual time=8,515.222..8,515.224 rows=10 loops=1)

  • Buffers: shared hit=2 read=195360
7. 256.888 8,515.220 ↓ 10.0 10 1

HashSetOp Intersect (cost=0.70..1,015,757.43 rows=1 width=20) (actual time=8,515.220..8,515.220 rows=10 loops=1)

  • Buffers: shared hit=2 read=195360
8. 143.193 8,258.332 ↓ 7.3 3,000,010 1

Append (cost=0.70..1,014,729.55 rows=411,152 width=20) (actual time=47.178..8,258.332 rows=3,000,010 loops=1)

  • Buffers: shared hit=2 read=195360
9. 0.013 47.529 ↓ 10.0 10 1

Subquery Scan on *SELECT* 2 (cost=0.70..8.54 rows=1 width=20) (actual time=47.176..47.529 rows=10 loops=1)

  • Buffers: shared read=7
10. 47.516 47.516 ↓ 10.0 10 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=47.164..47.516 rows=10 loops=1)

  • Index Cond: ((detail_type = 'BacNetProperty'::text) AND (name = 'BACnet Property 99'::text) AND (value = 'BACnet Property 99 value'::text))
  • Buffers: shared read=7
11. 253.883 8,067.610 ↓ 7.3 3,000,000 1

Subquery Scan on *SELECT* 1 (cost=20,322.87..1,012,665.25 rows=411,151 width=20) (actual time=1,014.353..8,067.610 rows=3,000,000 loops=1)

  • Buffers: shared hit=2 read=195353
12. 6,850.508 7,813.727 ↓ 7.3 3,000,000 1

Bitmap Heap Scan on entity_detail entity_detail_1 (cost=20,322.87..1,008,553.74 rows=411,151 width=16) (actual time=1,014.351..7,813.727 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=2 read=195353
13. 963.219 963.219 ↓ 7.3 3,000,000 1

Bitmap Index Scan on entity_detail_detail_type_name_value_idx (cost=0.00..20,220.08 rows=411,151 width=0) (actual time=963.219..963.219 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. 0.782 3,364.108 ↓ 2.2 891 1

Subquery Scan on *SELECT* 3 (cost=0.70..1,543.09 rows=397 width=20) (actual time=26.714..3,364.108 rows=891 loops=1)

  • Buffers: shared hit=287 read=613
15. 3,363.326 3,363.326 ↓ 2.2 891 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail entity_detail_2 (cost=0.70..1,539.12 rows=397 width=16) (actual time=26.711..3,363.326 rows=891 loops=1)

  • Index Cond: ((detail_type = 'Tag'::text) AND (name = 'Tag1'::text) AND (value = 'tag 2494'::text))
  • Buffers: shared hit=287 read=613
16. 1.066 3,970.595 ↓ 3.6 1,488 1

Subquery Scan on *SELECT* 4 (cost=0.70..1,589.22 rows=409 width=20) (actual time=30.182..3,970.595 rows=1,488 loops=1)

  • Buffers: shared hit=292 read=1159
17. 3,969.529 3,969.529 ↓ 3.6 1,488 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail entity_detail_3 (cost=0.70..1,585.13 rows=409 width=16) (actual time=30.180..3,969.529 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