explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dOwe : Optimization for: Optimization for: plan #bLX; plan #bAaM

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 5.791 33.576 ↓ 53.0 53 1

Nested Loop (cost=1,014.31..4,189.22 rows=1 width=209) (actual time=10.355..33.576 rows=53 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: 608
2. 0.055 18.461 ↓ 666.0 666 1

Nested Loop (cost=1,013.75..4,183.29 rows=1 width=411) (actual time=7.449..18.461 rows=666 loops=1)

3. 0.355 17.074 ↓ 666.0 666 1

Nested Loop (cost=1,013.47..4,182.98 rows=1 width=82) (actual time=7.441..17.074 rows=666 loops=1)

4. 0.284 15.387 ↓ 666.0 666 1

Nested Loop (cost=1,013.19..4,182.33 rows=1 width=82) (actual time=7.431..15.387 rows=666 loops=1)

5. 2.067 12.646 ↓ 819.0 819 1

Hash Right Join (cost=1,012.90..4,178.37 rows=1 width=50) (actual time=7.414..12.646 rows=819 loops=1)

  • Hash Cond: (hc.vehicle_id = v.id)
6. 3.184 3.184 ↑ 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.007..3.184 rows=4,979 loops=1)

7. 0.184 7.395 ↓ 819.0 819 1

Hash (cost=1,012.89..1,012.89 rows=1 width=50) (actual time=7.395..7.395 rows=819 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 74kB
8. 0.198 7.211 ↓ 819.0 819 1

Hash Right Join (cost=288.90..1,012.89 rows=1 width=50) (actual time=7.045..7.211 rows=819 loops=1)

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

Seq Scan on entity_tag et (cost=0.00..723.95 rows=8 width=8) (actual time=0.188..5.366 rows=61 loops=1)

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

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

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

Seq Scan on vehicle v (cost=0.00..278.54 rows=829 width=50) (actual time=0.015..1.478 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.332 1.332 ↑ 1.0 1 666

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

  • Index Cond: (id = va.dock_id)
14. 1.332 1.332 ↑ 1.0 1 666

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=666)

  • Index Cond: (id = d.dock_group_id)
15. 0.000 9.324 ↑ 1.0 1 666

Limit (cost=0.56..4.14 rows=1 width=104) (actual time=0.014..0.014 rows=1 loops=666)

16. 3.330 9.324 ↑ 15,471.0 1 666

Nested Loop (cost=0.56..55,276.52 rows=15,471 width=104) (actual time=0.014..0.014 rows=1 loops=666)

17. 5.994 5.994 ↑ 15,471.0 1 666

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

  • Index Cond: (vehicle_id = va.vehicle_id)
  • Filter: (position_source_id = 1)
18. 0.000 0.000 ↑ 1.0 1 661

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

19. 0.026 0.026 ↑ 1.0 1 1

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 1
Planning time : 2.215 ms
Execution time : 33.727 ms