explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bLX

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5.648 37.182 ↓ 43.0 43 1

Nested Loop (cost=1,008.16..4,197.29 rows=1 width=209) (actual time=11.419..37.182 rows=43 loops=1)

  • Join Filter: ((_st_distance(pl."position", dg.request_fences, '0'::double precision, true) > '50'::double precision) OR (_st_distance(pl."position", dg.lock_fences, '0'::double precision, true) > '50'::double precision))
  • Rows Removed by Join Filter: 605
2. 0.229 21.086 ↓ 653.0 653 1

Nested Loop (cost=1,007.60..4,177.13 rows=1 width=411) (actual time=8.175..21.086 rows=653 loops=1)

3. 0.493 19.551 ↓ 653.0 653 1

Nested Loop (cost=1,007.32..4,176.81 rows=1 width=82) (actual time=8.163..19.551 rows=653 loops=1)

4. 0.617 17.752 ↓ 653.0 653 1

Nested Loop (cost=1,007.04..4,176.18 rows=1 width=82) (actual time=8.151..17.752 rows=653 loops=1)

5. 2.419 14.678 ↓ 819.0 819 1

Hash Right Join (cost=1,006.75..4,172.22 rows=1 width=50) (actual time=8.131..14.678 rows=819 loops=1)

  • Hash Cond: (hc.vehicle_id = v.id)
6. 4.169 4.169 ↑ 1.0 4,979 1

Seq Scan on hardware_controller hc (cost=0.00..3,146.79 rows=4,979 width=4) (actual time=0.010..4.169 rows=4,979 loops=1)

7. 0.188 8.090 ↓ 819.0 819 1

Hash (cost=1,006.74..1,006.74 rows=1 width=50) (actual time=8.090..8.090 rows=819 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 74kB
8. 0.215 7.902 ↓ 819.0 819 1

Hash Right Join (cost=288.90..1,006.74 rows=1 width=50) (actual time=7.723..7.902 rows=819 loops=1)

  • Hash Cond: (et.entity_id = v.id)
  • Filter: (et.id IS NULL)
  • Rows Removed by Filter: 10
9. 5.679 5.679 ↓ 7.6 61 1

Seq Scan on entity_tag et (cost=0.00..717.80 rows=8 width=8) (actual time=0.183..5.679 rows=61 loops=1)

  • Filter: (((entity_kind)::text = 'Vehicle'::text) AND (tag_id = 2000000))
  • Rows Removed by Filter: 27959
10. 0.229 2.008 ↑ 1.0 829 1

Hash (cost=278.54..278.54 rows=829 width=50) (actual time=2.008..2.008 rows=829 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 75kB
11. 1.779 1.779 ↑ 1.0 829 1

Seq Scan on vehicle v (cost=0.00..278.54 rows=829 width=50) (actual time=0.018..1.779 rows=829 loops=1)

  • Filter: ((system_id)::text = 'bergen-city-bike'::text)
  • Rows Removed by Filter: 6253
12. 2.457 2.457 ↑ 1.0 1 819

Index Scan using vehicle_availability_vehicle_id on vehicle_availability va (cost=0.28..3.95 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=819)

  • Index Cond: (vehicle_id = v.id)
  • Filter: (dock_id IS NOT NULL)
  • Rows Removed by Filter: 0
13. 1.306 1.306 ↑ 1.0 1 653

Index Scan using dock_pkey on dock d (cost=0.29..0.62 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=653)

  • Index Cond: (id = va.dock_id)
14. 1.306 1.306 ↑ 1.0 1 653

Index Scan using dock_group_pkey on dock_group dg (cost=0.28..0.31 rows=1 width=333) (actual time=0.001..0.002 rows=1 loops=653)

  • Index Cond: (id = d.dock_group_id)
15. 0.653 10.448 ↑ 1.0 1 653

Limit (cost=0.56..18.38 rows=1 width=104) (actual time=0.015..0.016 rows=1 loops=653)

16. 3.265 9.795 ↑ 3,094.0 1 653

Nested Loop (cost=0.56..55,121.81 rows=3,094 width=104) (actual time=0.015..0.015 rows=1 loops=653)

  • Join Filter: (pl.position_source_id = ps.id)
17. 6.530 6.530 ↑ 15,471.0 1 653

Index Scan using position_log_vehicle_position_idx on position_log_data pl (cost=0.56..54,850.01 rows=15,471 width=44) (actual time=0.010..0.010 rows=1 loops=653)

  • Index Cond: (vehicle_id = va.vehicle_id)
18. 0.000 0.000 ↑ 1.0 1 648

Materialize (cost=0.00..1.07 rows=1 width=2) (actual time=0.000..0.000 rows=1 loops=648)

19. 0.039 0.039 ↑ 1.0 1 1

Seq Scan on position_source ps (cost=0.00..1.06 rows=1 width=2) (actual time=0.011..0.039 rows=1 loops=1)

  • Filter: (position_source = 'controller_gps'::text)
  • Rows Removed by Filter: 1
Planning time : 2.386 ms
Execution time : 37.499 ms