explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XjPk

Settings
# exclusive inclusive rows x rows loops node
1. 10.180 84,820.600 ↑ 1.0 10 1

Limit (cost=497,269.24..497,314.82 rows=10 width=69) (actual time=84,111.487..84,820.600 rows=10 loops=1)

  • Buffers: shared hit=880 read=1582518
2. 0.425 84,810.420 ↑ 15,375.1 10 1

Merge Join (cost=497,269.24..1,197,972.72 rows=153,751 width=69) (actual time=84,111.485..84,810.420 rows=10 loops=1)

  • Merge Cond: (entity.id = "*SELECT* 2".entity_id)
  • Buffers: shared hit=880 read=1582518
3. 907.283 907.283 ↑ 77,163.5 110 1

Index Scan using entity_pkey on entity (cost=0.43..677,177.69 rows=8,487,981 width=69) (actual time=28.407..907.283 rows=110 loops=1)

  • Buffers: shared read=113
4. 354.337 83,902.712 ↑ 15,375.1 10 1

Sort (cost=497,268.81..497,653.19 rows=153,751 width=16) (actual time=83,902.703..83,902.712 rows=10 loops=1)

  • Sort Key: "*SELECT* 2".entity_id
  • Sort Method: quicksort Memory: 72590kB
  • Buffers: shared hit=880 read=1582405
5. 641.551 83,548.375 ↓ 6.7 1,024,296 1

HashAggregate (cost=480,947.96..482,485.47 rows=153,751 width=16) (actual time=83,365.852..83,548.375 rows=1,024,296 loops=1)

  • Group Key: "*SELECT* 2".entity_id
  • Buffers: shared hit=880 read=1582405
6. 51.254 82,906.824 ↓ 6.7 1,024,296 1

Append (cost=0.70..480,563.58 rows=153,751 width=16) (actual time=62,547.999..82,906.824 rows=1,024,296 loops=1)

  • Buffers: shared hit=880 read=1582405
7. 0.126 62,704.324 ↓ 285.0 285 1

Result (cost=0.70..1,554.63 rows=1 width=16) (actual time=62,547.998..62,704.324 rows=285 loops=1)

  • Buffers: shared read=1232157
8. 1,849.394 62,704.198 ↓ 285.0 285 1

HashSetOp Intersect (cost=0.70..1,554.62 rows=1 width=20) (actual time=62,547.996..62,704.198 rows=285 loops=1)

  • Buffers: shared read=1232157
9. 192.759 60,854.804 ↓ 7,539.9 3,000,891 1

Append (cost=0.70..1,553.62 rows=398 width=20) (actual time=43.543..60,854.804 rows=3,000,891 loops=1)

  • Buffers: shared read=1232157
10. 315.325 55,604.913 ↓ 3,000,000.0 3,000,000 1

Subquery Scan on *SELECT* 2 (cost=0.70..8.54 rows=1 width=20) (actual time=43.543..55,604.913 rows=3,000,000 loops=1)

  • Buffers: shared read=1231257
11. 55,289.588 55,289.588 ↓ 3,000,000.0 3,000,000 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=43.542..55,289.588 rows=3,000,000 loops=1)

  • Index Cond: ((detail_type = 'BacNetProperty'::text) AND (name = 'BACnet Property 6'::text) AND (value = 'BACnet Property 6 value'::text))
  • Buffers: shared read=1231257
12. 1.067 5,057.132 ↓ 2.2 891 1

Subquery Scan on *SELECT* 1 (cost=0.70..1,543.09 rows=397 width=20) (actual time=54.801..5,057.132 rows=891 loops=1)

  • Buffers: shared read=900
13. 5,056.065 5,056.065 ↓ 2.2 891 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail entity_detail_1 (cost=0.70..1,539.12 rows=397 width=16) (actual time=54.799..5,056.065 rows=891 loops=1)

  • Index Cond: ((detail_type = 'Tag'::text) AND (name = 'Tag1'::text) AND (value = 'tag 2494'::text))
  • Buffers: shared read=900
14. 0.005 9,988.164 ↑ 6.0 11 1

Result (cost=0.70..1,855.88 rows=66 width=16) (actual time=9,984.639..9,988.164 rows=11 loops=1)

  • Buffers: shared hit=6 read=101780
15. 71.479 9,988.159 ↑ 6.0 11 1

HashSetOp Intersect (cost=0.70..1,855.22 rows=66 width=20) (actual time=9,984.637..9,988.159 rows=11 loops=1)

  • Buffers: shared hit=6 read=101780
16. 10.889 9,916.680 ↓ 211.8 100,839 1

Append (cost=0.70..1,854.03 rows=476 width=20) (actual time=29.321..9,916.680 rows=100,839 loops=1)

  • Buffers: shared hit=6 read=101780
17. 17.827 5,208.432 ↓ 1,515.2 100,000 1

Subquery Scan on *SELECT* 3 (cost=0.70..258.58 rows=66 width=20) (actual time=29.319..5,208.432 rows=100,000 loops=1)

  • Buffers: shared hit=2 read=100937
18. 5,190.605 5,190.605 ↓ 1,515.2 100,000 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail entity_detail_2 (cost=0.70..257.92 rows=66 width=16) (actual time=29.317..5,190.605 rows=100,000 loops=1)

  • Index Cond: ((detail_type = 'Parameter'::text) AND (name = 'Device Parameter 2'::text) AND (value = 'Device Parameter 2 value'::text))
  • Buffers: shared hit=2 read=100937
19. 1.080 4,697.359 ↓ 2.0 839 1

Subquery Scan on *SELECT* 4 (cost=0.70..1,593.07 rows=410 width=20) (actual time=14.270..4,697.359 rows=839 loops=1)

  • Buffers: shared hit=4 read=843
20. 4,696.279 4,696.279 ↓ 2.0 839 1

Index Scan using entity_detail_detail_type_name_value_idx on entity_detail entity_detail_3 (cost=0.70..1,588.97 rows=410 width=16) (actual time=14.269..4,696.279 rows=839 loops=1)

  • Index Cond: ((detail_type = 'Tag'::text) AND (name = 'Tag2'::text) AND (value = 'tag 6289'::text))
  • Buffers: shared hit=4 read=843
21. 9,724.665 10,163.082 ↓ 6.7 1,024,000 1

Bitmap Heap Scan on entity_detail entity_detail_4 (cost=7,592.17..474,847.48 rows=153,684 width=16) (actual time=502.467..10,163.082 rows=1,024,000 loops=1)

  • Recheck Cond: ((detail_type = 'EntityProperty'::text) AND (name = 'DisplayName'::text) AND (value = 'I/O Channel 2'::text))
  • Heap Blocks: exact=240943
  • Buffers: shared hit=874 read=248468
22. 438.417 438.417 ↓ 6.7 1,024,000 1

Bitmap Index Scan on entity_detail_detail_type_name_value_idx (cost=0.00..7,553.74 rows=153,684 width=0) (actual time=438.416..438.417 rows=1,024,000 loops=1)

  • Index Cond: ((detail_type = 'EntityProperty'::text) AND (name = 'DisplayName'::text) AND (value = 'I/O Channel 2'::text))
  • Buffers: shared hit=2 read=8397