explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fZrn

Settings
# exclusive inclusive rows x rows loops node
1. 4.219 18.860 ↓ 5.5 562 1

Sort (cost=3,154.63..3,154.88 rows=102 width=501) (actual time=18.809..18.860 rows=562 loops=1)

  • Sort Key: (CASE WHEN ((dock.type)::text = 'virtual'::text) THEN 0 ELSE 1 END), (CASE WHEN (("vehicle->controller".power_state)::text = 'on'::text) THEN 0 ELSE 1 END), vehicle.priority DESC, "vehicle->controller".battery_charge
  • Sort Method: quicksort Memory: 314kB
2. 0.492 14.641 ↓ 5.5 562 1

Nested Loop (cost=522.49..3,151.22 rows=102 width=501) (actual time=1.856..14.641 rows=562 loops=1)

  • Join Filter: ("VehicleAvailability".vehicle_id = "vehicle->controller".vehicle_id)
3. 0.341 12.391 ↓ 2.2 586 1

Nested Loop (cost=522.21..2,488.32 rows=268 width=157) (actual time=1.830..12.391 rows=586 loops=1)

4. 0.000 10.850 ↓ 1.6 600 1

Nested Loop (cost=521.93..2,274.27 rows=379 width=36) (actual time=1.819..10.850 rows=600 loops=1)

5. 1.465 7.440 ↓ 1.0 1,822 1

Hash Join (cost=521.52..1,042.71 rows=1,756 width=24) (actual time=1.788..7.440 rows=1,822 loops=1)

  • Hash Cond: (dock.dock_group_id = "dock->dockGroup".id)
6. 4.242 5.853 ↑ 1.1 14,691 1

Bitmap Heap Scan on dock (cost=449.38..891.71 rows=16,343 width=16) (actual time=1.653..5.853 rows=14,691 loops=1)

  • Recheck Cond: (deleted_at IS NULL)
  • Filter: ((broken_at IS NULL) AND ((state)::text = 'active'::text))
  • Rows Removed by Filter: 215
  • Heap Blocks: exact=232
7. 1.611 1.611 ↑ 1.1 14,909 1

Bitmap Index Scan on dock_group_deleted_at_idx (cost=0.00..445.29 rows=16,667 width=0) (actual time=1.611..1.611 rows=14,909 loops=1)

  • Index Cond: (deleted_at IS NULL)
8. 0.017 0.122 ↑ 1.1 80 1

Hash (cost=71.01..71.01 rows=91 width=12) (actual time=0.122..0.122 rows=80 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
9. 0.082 0.105 ↑ 1.1 80 1

Bitmap Heap Scan on dock_group "dock->dockGroup" (cost=9.06..71.01 rows=91 width=12) (actual time=0.030..0.105 rows=80 loops=1)

  • Recheck Cond: ((system_id)::text = 'trondheim'::text)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 21
  • Heap Blocks: exact=24
10. 0.023 0.023 ↑ 1.0 101 1

Bitmap Index Scan on dock_group_system_id_idx (cost=0.00..9.03 rows=101 width=0) (actual time=0.023..0.023 rows=101 loops=1)

  • Index Cond: ((system_id)::text = 'trondheim'::text)
11. 3.644 3.644 ↓ 0.0 0 1,822

Index Scan using dock_must_not_be_taken on vehicle_availability "VehicleAvailability" (cost=0.41..0.69 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1,822)

  • Index Cond: (dock_id = dock.id)
  • Filter: ((deleted_at IS NULL) AND ((state)::text = 'docked'::text))
12. 1.200 1.200 ↑ 1.0 1 600

Index Scan using vehicle_pkey on vehicle (cost=0.28..0.55 rows=1 width=121) (actual time=0.002..0.002 rows=1 loops=600)

  • Index Cond: (id = "VehicleAvailability".vehicle_id)
  • Filter: ((deleted_at IS NULL) AND ((state)::text = 'active'::text))
  • Rows Removed by Filter: 0
13. 1.758 1.758 ↑ 1.0 1 586

Index Scan using hardware_controller_vehicle_id_idx on hardware_controller "vehicle->controller" (cost=0.28..2.46 rows=1 width=278) (actual time=0.003..0.003 rows=1 loops=586)

  • Index Cond: (vehicle_id = vehicle.id)
  • Filter: ((battery_voltage > '3.78'::double precision) AND ((state)::text = 'online'::text) AND ((power_state)::text = ANY ('{on,sleep,snooze}'::text[])))
  • Rows Removed by Filter: 0
Planning time : 1.352 ms
Execution time : 19.243 ms