explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MDE9

Settings
# exclusive inclusive rows x rows loops node
1. 0.066 15,892.655 ↓ 10.0 10 1

Nested Loop (cost=1,018,896.28..1,018,904.31 rows=1 width=45) (actual time=15,651.820..15,892.655 rows=10 loops=1)

  • Buffers: shared hit=594 read=197162
2. 0.021 15,576.989 ↓ 10.0 10 1

Unique (cost=1,018,895.84..1,018,895.85 rows=1 width=16) (actual time=15,576.960..15,576.989 rows=10 loops=1)

  • Buffers: shared hit=584 read=197132
3. 0.086 15,576.968 ↓ 10.0 10 1

Sort (cost=1,018,895.84..1,018,895.85 rows=1 width=16) (actual time=15,576.959..15,576.968 rows=10 loops=1)

  • Sort Key: t.entity_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=584 read=197132
4. 0.002 15,576.882 ↓ 10.0 10 1

Subquery Scan on t (cost=0.70..1,018,895.83 rows=1 width=16) (actual time=15,576.879..15,576.882 rows=10 loops=1)

  • Buffers: shared hit=581 read=197132
5. 1.252 15,576.880 ↓ 10.0 10 1

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

  • Buffers: shared hit=581 read=197132
6. 0.634 15,575.628 ↓ 3.7 1,498 1

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

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

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

  • Buffers: shared hit=289 read=195973
8. 1.141 11,704.200 ↓ 10.0 10 1

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

  • Buffers: shared hit=289 read=195973
9. 0.491 11,703.059 ↓ 2.3 901 1

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

  • Buffers: shared hit=289 read=195973
10. 0.003 8,280.025 ↓ 10.0 10 1

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

  • Buffers: shared hit=2 read=195360
11. 269.247 8,280.022 ↓ 10.0 10 1

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

  • Buffers: shared hit=2 read=195360
12. 150.320 8,010.775 ↓ 7.3 3,000,010 1

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

  • Buffers: shared hit=2 read=195360
13. 0.028 45.829 ↓ 10.0 10 1

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

  • Buffers: shared read=7
14. 45.801 45.801 ↓ 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=45.434..45.801 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
15. 264.162 7,814.626 ↓ 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.356..7,814.626 rows=3,000,000 loops=1)

  • Buffers: shared hit=2 read=195353
16. 6,587.221 7,550.464 ↓ 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.354..7,550.464 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
17. 963.243 963.243 ↓ 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.243..963.243 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
18. 0.724 3,422.543 ↓ 2.2 891 1

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

  • Buffers: shared hit=287 read=613
19. 3,421.819 3,421.819 ↓ 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.783..3,421.819 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
20. 1.146 3,870.792 ↓ 3.6 1,488 1

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

  • Buffers: shared hit=292 read=1159
21. 3,869.646 3,869.646 ↓ 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.156..3,869.646 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
22. 315.600 315.600 ↑ 1.0 1 10

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

  • Index Cond: (id = t.entity_id)
  • Buffers: shared hit=10 read=30