explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PJMw

Settings
# exclusive inclusive rows x rows loops node
1. 0.061 15,737.853 ↓ 10.0 10 1

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

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

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

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

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

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

Subquery Scan on temp (cost=0.70..1,018,895.83 rows=1 width=16) (actual time=15,463.910..15,463.914 rows=10 loops=1)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Buffers: shared read=7
14. 49.376 49.376 ↓ 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=49.045..49.376 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.666 7,890.871 ↓ 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.378..7,890.871 rows=3,000,000 loops=1)

  • Buffers: shared hit=2 read=195353
16. 6,662.655 7,626.205 ↓ 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.376..7,626.205 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.550 963.550 ↓ 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.550..963.550 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.733 3,414.300 ↓ 2.2 891 1

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

  • Buffers: shared hit=287 read=613
19. 3,413.567 3,413.567 ↓ 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.759..3,413.567 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.173 3,695.280 ↓ 3.6 1,488 1

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

  • Buffers: shared hit=292 read=1159
21. 3,694.107 3,694.107 ↓ 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.152..3,694.107 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. 273.730 273.730 ↑ 1.0 1 10

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

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