explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bAaM : Optimization for: plan #bLX

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5.291 33.017 ↓ 51.0 51 1

Nested Loop (cost=1,003.60..4,193.36 rows=1 width=177) (actual time=10.869..33.017 rows=51 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: 604
2. 0.102 19.146 ↓ 660.0 660 1

Nested Loop (cost=1,003.04..4,187.46 rows=1 width=411) (actual time=7.982..19.146 rows=660 loops=1)

3. 0.386 17.724 ↓ 660.0 660 1

Nested Loop (cost=1,002.76..4,187.14 rows=1 width=82) (actual time=7.971..17.724 rows=660 loops=1)

4. 0.178 16.018 ↓ 660.0 660 1

Nested Loop (cost=1,002.48..4,186.49 rows=1 width=82) (actual time=7.960..16.018 rows=660 loops=1)

5. 2.162 13.383 ↓ 819.0 819 1

Hash Right Join (cost=1,002.19..4,182.53 rows=1 width=50) (actual time=7.941..13.383 rows=819 loops=1)

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

Seq Scan on hardware_controller hc (cost=0.00..3,157.60 rows=6,060 width=4) (actual time=0.009..3.326 rows=4,979 loops=1)

7. 0.190 7.895 ↓ 819.0 819 1

Hash (cost=1,002.18..1,002.18 rows=1 width=50) (actual time=7.895..7.895 rows=819 loops=1)

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

Hash Right Join (cost=288.90..1,002.18 rows=1 width=50) (actual time=7.528..7.705 rows=819 loops=1)

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

Seq Scan on entity_tag et (cost=0.00..713.24 rows=8 width=8) (actual time=0.193..5.536 rows=61 loops=1)

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

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

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

Seq Scan on vehicle v (cost=0.00..278.54 rows=829 width=50) (actual time=0.017..1.780 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.320 1.320 ↑ 1.0 1 660

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

  • Index Cond: (id = va.dock_id)
14. 1.320 1.320 ↑ 1.0 1 660

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

  • Index Cond: (id = d.dock_group_id)
15. 0.660 8.580 ↑ 1.0 1 660

Limit (cost=0.56..4.12 rows=1 width=72) (actual time=0.012..0.013 rows=1 loops=660)

16. 7.920 7.920 ↑ 15,471.0 1 660

Index Scan using position_log_vehicle_position_idx on position_log_data pl (cost=0.56..54,927.36 rows=15,471 width=72) (actual time=0.012..0.012 rows=1 loops=660)

  • Index Cond: (vehicle_id = va.vehicle_id)
  • Filter: (position_source_id = 1)
Planning time : 2.058 ms
Execution time : 33.178 ms