explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GoQE

Settings
# exclusive inclusive rows x rows loops node
1. 1.551 22,916.914 ↓ 1.4 285 1

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

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

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

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

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

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

Subquery Scan on temp (cost=0.70..1,019,688.80 rows=397 width=16) (actual time=17,215.390..17,215.421 rows=285 loops=1)

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

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

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

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

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

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

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

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

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

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

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

Subquery Scan on *SELECT* 2 (cost=0.70..1,543.09 rows=397 width=20) (actual time=50.593..5,061.419 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.584..5060.406 rows=891 loops=1) Index Cond: ((detail_type = 'Tag'::text) AND (name = 'Tag1'::text) AND (value = 'tag 2494'::text))
  • Buffers: shared read=900
11. 252.868 7,887.985 ↓ 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,001.218..7,887.985 rows=3,000,000 loops=1)

  • Buffers: shared hit=287 read=195068
12. 7,635.117 7,635.117 ↓ 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,001.217..7,635.117 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
  • -> Bitmap Index Scan on entity_detail_detail_type_name_value_idx (cost=0.00..20226.06 rows=411629 width=0) (actual time=942.789..942.789 rows=3000000 loops=1) Index Cond: ((detail_type = 'EntityProperty'::text) AND (name = 'EntityType'::text) AND (value = 'BACnet'::text))
  • Buffers: shared hit=2 read=21431
13. 1.090 3,735.419 ↓ 3.6 1,488 1

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

  • Buffers: shared hit=292 read=1159
14. 3,734.329 3,734.329 ↓ 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=44.994..3,734.329 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
15. 5,698.860 5,698.860 ↑ 1.0 1 285

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

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