Add (car_id, date) btree on positions to speed up bundled Grafana dashboards

[benholtz]

Summary

Several built-in Grafana dashboards (and presumably the Phoenix UI) issue queries against positions of the form:

SELECT … FROM positions WHERE car_id = ? ORDER BY date DESC LIMIT 1;
SELECT … FROM positions WHERE car_id = ? AND date >= NOW() - INTERVAL '…' …;

There is no btree index that covers this access pattern in the general case. The existing relevant indexes on positions are:

Index	Covers
positions_pkey (id)	row lookups by id
positions_car_id_index (car_id)	car_id only — no help for ORDER BY date DESC
positions_car_id_date__ideal_battery_range_km_IS_NOT_NULL_index (car_id, date) WHERE ideal_battery_range_km IS NOT NULL	only queries that explicitly include ideal_battery_range_km IS NOT NULL
positions_date_index	BRIN on date alone
positions_drive_id_date_index	BRIN on (drive_id, date)

The result: any "latest-by-time-per-car" or "recent-window-per-car" query that does not include ideal_battery_range_km IS NOT NULL falls back to a Parallel Seq Scan over the entire positions table.

Reproduction

On a single-car deployment with 842,860 rows in positions (84 MB table), PostgreSQL 17.3:

EXPLAIN (ANALYZE, BUFFERS)
SELECT usable_battery_level, date
FROM positions
WHERE car_id = 1 AND usable_battery_level IS NOT NULL
ORDER BY date DESC
LIMIT 1;

(This is the exact query from the bundled battery-health.json dashboard, panel "State of Health".)

Before (no (car_id, date DESC) index):

Limit  (actual time=1794..1894 rows=1)
  -> Gather Merge ... Workers Planned: 2
       -> Sort (Sort Method: top-N heapsort)
            -> Parallel Seq Scan on positions (rows=280953/loop x 3)
                 Filter: (car_id = 1)
Execution Time: 1894.561 ms

After adding CREATE INDEX CONCURRENTLY idx_positions_car_id_date_desc ON positions (car_id, date DESC):

Limit  (actual time=0.055..0.056 rows=1)
  Buffers: shared hit=7
  -> Index Scan using idx_positions_car_id_date_desc on positions
       Index Cond: (car_id = 1)
       Filter: (usable_battery_level IS NOT NULL)
Execution Time: 0.085 ms

Roughly 22,000× faster on this query. Index size on a single-car dataset: 25 MB.

Affected built-in dashboards / queries

Greppable matches in grafana/dashboards/*.json for WHERE car_id = $car_id … ORDER BY date DESC LIMIT 1 (or range variants without the partial-index filter):

• battery-health.json — "State of Health" (2 queries)
• overview.json — outside_temp last-60m latest reading; several other latest-row lookups
• trip.json — WHERE car_id = ? AND date BETWEEN …
• trackingdrives.json — same shape, multiple panels
• drive-stats.json — speed-section aggregations
• visited.json — date-bucketed lat/long over windows
• drive-details.json — multiple ORDER BY date ASC over WHERE car_id = ? AND timefilter

Queries that already include ideal_battery_range_km IS NOT NULL (charge-level, projected-range, parts of overview) are correctly served by the existing partial index and would be unaffected.

The Phoenix UI's "current state" view is also a likely beneficiary — any "latest position for car X" path goes through this shape — though I have not profiled the Elixir side directly.

Proposed change

Add a regular composite btree to the schema via a new Ecto migration:

defmodule TeslaMate.Repo.Migrations.AddPositionsCarIdDateDescIndex do
  use Ecto.Migration

  @disable_ddl_transaction true
  @disable_migration_lock true

  def change do
    create_if_not_exists index(
      :positions,
      [:car_id, :date],
      name: :positions_car_id_date_desc_index,
      concurrently: true
    )
  end
end

Notes:

• CONCURRENTLY so existing installations can migrate without locking the positions table (running deployments keep ingesting).
• DESC vs ASC: PostgreSQL btrees can scan in either direction, so the column-order index serves both ORDER BY date DESC LIMIT N and ORDER BY date ASC ranges. (I used DESC in the manual SQL above only as a hint to the planner — the migration above using default ASC works equivalently for the planner.)
• Build time on a 842k-row / single-car dataset: ~10 s. Larger / multi-car installs will scale roughly linearly.
• Index size estimate: 25 MB on this dataset (single-car, n_distinct=1 keeps it very compact). Multi-car installs will be larger but still bounded by the row count.
• Write amplification: append-mostly workload (~1 row/sec while driving, near-zero when parked) makes the maintenance cost negligible.
• Optional follow-up: drop positions_car_id_index once the new composite is in place — it's redundant for any car_id lookup (the new index has the same leading column) and pg_stat_user_indexes showed 0 scans against it on this install. Out of scope for this PR; would want broader confirmation first.

Related issues / prior art

• PR #5075 (merged 2026-01-08, perf: Replace positions indexes from BTREE to BRIN to reduce memory usage) — converted positions_drive_id_date_index and positions_date_index from BTREE to BRIN. Kept positions_car_id_index (single-column on car_id) and the (car_id, date) WHERE ideal_battery_range_km IS NOT NULL partial index. This proposal does not undo that work — the (car_id, date) composite proposed here never existed; it's a new index, not a regression of #5075.
• Issue #5172 (closed 2026-03-14, Severe UI Lag and High IO on Grafana Pages After Upgrade to v3.0.0) — user reported Battery Health and Visited dashboards loading much slower after the v3.0.0 upgrade (which shipped #5075). Comment from @vincep5 on 2026-03-08 specifically asks whether the BRIN/BTREE swap is responsible. The issue was closed as a duplicate of #5178, but #5178 is an unrelated accept-language/BEAM CPU lock — so the actual Battery Health slowness reported in #5172 was not addressed. The query plans above explain the symptom and the proposed index resolves it.
• PR #5276 (merged 2026-04-11, fix: improve brin indexes (autosummarize + timestamp_minmax_multi_ops)) — followup tuning on the BRIN swap. Helpful for date-range scans, but doesn't help WHERE car_id = ? queries because BRIN on date alone has no notion of car_id and positions_car_id_index is single-column. The composite is the missing piece.
• PR #4964 (merged 2025-09-24, perf: Optimize Grafana query for trip view to leverage indexes more effectively) — precedent for fixing this class of slowness at the query level. The proposal here addresses the index side instead, which avoids touching every dashboard.
• PR #3801 (closed 2024-04-05, perf: Positions table index on Odometer and Car_ID …) — earlier index proposal. Different columns (odometer + car_id), no EXPLAIN evidence; closed quickly. Mentioned only for completeness.

Environment

• TeslaMate 1.32.x (current release line)
• PostgreSQL 17.3 (CloudNativePG-managed cluster on k3s)
• 842k positions, single-car install, ~3.5 years of history
• Discovered while profiling a downstream consumer that runs the same access pattern; rolled out the index locally and saw broad latency drops across the bundled Grafana dashboards as a side benefit.

[JakobLichterfeld]

Nice finding. We will definitely look into it and take the speed improvements. :-)

[swiffer]

thanks for diving into this! - i do see 2 ways going forward:

• add another index on the positions table
• reuse the existing index in more places (rewriting the queries, see example below) -> https://docs.teslamate.org/docs/development#streaming-api-data--positions-table-usage-in-dashboard-queries

i guess it's caused by migrating the existing date index from b-tree to brin which is no longer sorted by default and postgres now no longer considering the index for this kind of queries.

EXPLAIN (ANALYZE, BUFFERS)
SELECT usable_battery_level, date
FROM positions
WHERE car_id = 2 AND ideal_battery_range_km IS NOT NULL
ORDER BY date DESC
LIMIT 1;

creating the index manually in my instance to check the size: 340mb (1.1gb of data, 3 cars)

will have a look on what queries can be rewritten or if need to add the index back

[swiffer]

the Elixir functions for get_latest_position (https://github.com/teslamate-org/teslamate/blob/main/lib/teslamate/log.ex#L158-L171) are affected as well.

• get_latest_position is used as initial position for the map when creating a geofence - i think it's save to change that to order_by(desc: :id) instead of (order_by(desc: :date)
• get_latest_position(%Car{id: id}) is used in restore_last_known_values(%Vehicle{} = vehicle, data) - not sure if we can simply add the ideal_battery_range_km IS NOT NULL condition here or if that has unwanted side effects (@brianmay / @JakobLichterfeld)

the second query is having the greatest mean exec time in my instance now (looks like beeing run on teslamate startup once only).

before changing dashboards i would like to make sure the get_latest_position is safe to be changed - if we end of up reverting the index to btree there is no need in changing the dashboards.

[JakobLichterfeld]

Nor sure if I like the idea of using id instead of date ordering. Normally this should result in the same, but what if a drive is not closed or other edge cases. last known position should be date related not id imo.
That being said, I would love a dashboard only solution.