explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ynC6 : Optimization for: Optimization for: plan #y46a; plan #qTXb

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.801 25.511 ↓ 2.4 948 1

Nested Loop (cost=242.57..2,308.63 rows=402 width=101) (actual time=1.696..25.511 rows=948 loops=1)

  • Buffers: shared hit=7818
2. 0.437 6.030 ↓ 2.4 980 1

Hash Left Join (cost=241.86..688.69 rows=402 width=65) (actual time=1.624..6.030 rows=980 loops=1)

  • Hash Cond: (d.dock_group_id = dg.id)
  • Buffers: shared hit=1050
3. 0.839 5.050 ↓ 2.4 980 1

Nested Loop Left Join (cost=178.66..619.97 rows=402 width=44) (actual time=1.072..5.050 rows=980 loops=1)

  • Buffers: shared hit=1001
4. 1.007 3.231 ↓ 2.4 980 1

Hash Join (cost=178.38..323.52 rows=402 width=32) (actual time=1.068..3.231 rows=980 loops=1)

  • Hash Cond: (va.vehicle_id = v.id)
  • Buffers: shared hit=172
5. 1.184 1.184 ↓ 1.0 2,119 1

Seq Scan on vehicle_availability va (cost=0.00..133.18 rows=2,118 width=21) (actual time=0.014..1.184 rows=2,119 loops=1)

  • Buffers: shared hit=112
6. 0.238 1.040 ↑ 1.0 980 1

Hash (cost=166.13..166.13 rows=980 width=11) (actual time=1.040..1.040 rows=980 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
  • Buffers: shared hit=60
7. 0.623 0.802 ↑ 1.0 980 1

Bitmap Heap Scan on vehicle v (cost=39.88..166.13 rows=980 width=11) (actual time=0.195..0.802 rows=980 loops=1)

  • Recheck Cond: ((system_id)::text = 'edinburgh-city-bikes'::text)
  • Heap Blocks: exact=49
  • Buffers: shared hit=60
8. 0.179 0.179 ↑ 1.0 980 1

Bitmap Index Scan on number_must_be_unique_per_system (cost=0.00..39.63 rows=980 width=0) (actual time=0.179..0.179 rows=980 loops=1)

  • Index Cond: ((system_id)::text = 'edinburgh-city-bikes'::text)
  • Buffers: shared hit=11
9. 0.980 0.980 ↓ 0.0 0 980

Index Scan using dock_pkey on dock d (cost=0.29..0.73 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=980)

  • Index Cond: (va.dock_id = id)
  • Buffers: shared hit=829
10. 0.180 0.543 ↑ 1.0 631 1

Hash (cost=55.31..55.31 rows=631 width=25) (actual time=0.543..0.543 rows=631 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
  • Buffers: shared hit=49
11. 0.363 0.363 ↑ 1.0 631 1

Seq Scan on dock_group dg (cost=0.00..55.31 rows=631 width=25) (actual time=0.007..0.363 rows=631 loops=1)

  • Buffers: shared hit=49
12. 0.980 15.680 ↑ 1.0 1 980

Limit (cost=0.71..4.01 rows=1 width=40) (actual time=0.016..0.016 rows=1 loops=980)

  • Buffers: shared hit=6768
13. 1.012 14.700 ↑ 9,886.0 1 980

Nested Loop (cost=0.71..32,596.92 rows=9,886 width=40) (actual time=0.015..0.015 rows=1 loops=980)

  • Buffers: shared hit=6768
14. 12.740 12.740 ↑ 9,886.0 1 980

Index Scan using position_log_vehicle_position_idx on position_log_data pl (cost=0.56..30,794.15 rows=9,886 width=42) (actual time=0.013..0.013 rows=1 loops=980)

  • Index Cond: (vehicle_id = va.vehicle_id)
  • Buffers: shared hit=4872
15. 0.948 0.948 ↑ 1.0 1 948

Index Only Scan using position_source_pkey on position_source ps (cost=0.15..0.17 rows=1 width=2) (actual time=0.001..0.001 rows=1 loops=948)

  • Index Cond: (id = pl.position_source_id)
  • Heap Fetches: 948
  • Buffers: shared hit=1896