search

teslamate-org / teslamate

A self-hosted data logger for your Tesla 🚘
https://docs.teslamate.org
MIT License
5.77k stars 720 forks source link

perf: check migrations for indexes, add if necessary #4201

Open JakobLichterfeld opened 2 days ago

JakobLichterfeld commented 2 days ago

We currenty do have 32 indexes for performance reasons.

In my instance there were only 28 (installation from 2019).

We can add them by:

corrective sql (https://github.com/teslamate-org/teslamate/pull/4187#issuecomment-2351425969)

CREATE UNIQUE INDEX IF NOT EXISTS addresses_osm_id_osm_type_index ON public.addresses USING btree (osm_id, osm_type);
CREATE UNIQUE INDEX IF NOT EXISTS addresses_pkey ON public.addresses USING btree (id);
CREATE UNIQUE INDEX IF NOT EXISTS car_settings_pkey ON public.car_settings USING btree (id);
CREATE UNIQUE INDEX IF NOT EXISTS cars_eid_index ON public.cars USING btree (eid);
CREATE UNIQUE INDEX IF NOT EXISTS cars_pkey ON public.cars USING btree (id);
CREATE UNIQUE INDEX IF NOT EXISTS cars_settings_id_index ON public.cars USING btree (settings_id);
CREATE UNIQUE INDEX IF NOT EXISTS cars_vid_index ON public.cars USING btree (vid);
CREATE UNIQUE INDEX IF NOT EXISTS cars_vin_index ON public.cars USING btree (vin);
CREATE INDEX IF NOT EXISTS charges_charging_process_id_index ON public.charges USING btree (charging_process_id);
CREATE INDEX IF NOT EXISTS charges_date_index ON public.charges USING btree (date);
CREATE UNIQUE INDEX IF NOT EXISTS charges_pkey ON public.charges USING btree (id);
CREATE INDEX IF NOT EXISTS charging_processes_address_id_index ON public.charging_processes USING btree (address_id);
CREATE INDEX IF NOT EXISTS charging_processes_car_id_index ON public.charging_processes USING btree (car_id);
CREATE UNIQUE INDEX IF NOT EXISTS charging_processes_pkey ON public.charging_processes USING btree (id);
CREATE INDEX IF NOT EXISTS charging_processes_position_id_index ON public.charging_processes USING btree (position_id);
CREATE INDEX IF NOT EXISTS drives_end_geofence_id_index ON public.drives USING btree (end_geofence_id);
CREATE INDEX IF NOT EXISTS drives_end_position_id_index ON public.drives USING btree (end_position_id);
CREATE INDEX IF NOT EXISTS drives_start_geofence_id_index ON public.drives USING btree (start_geofence_id);
CREATE INDEX IF NOT EXISTS drives_start_position_id_index ON public.drives USING btree (start_position_id);
CREATE INDEX IF NOT EXISTS trips_car_id_index ON public.drives USING btree (car_id);
CREATE INDEX IF NOT EXISTS trips_end_address_id_index ON public.drives USING btree (end_address_id);
CREATE UNIQUE INDEX IF NOT EXISTS trips_pkey ON public.drives USING btree (id);
CREATE INDEX IF NOT EXISTS trips_start_address_id_index ON public.drives USING btree (start_address_id);
CREATE UNIQUE INDEX IF NOT EXISTS geofences_pkey ON public.geofences USING btree (id);
CREATE INDEX IF NOT EXISTS positions_car_id_index ON public.positions USING btree (car_id);
CREATE INDEX IF NOT EXISTS positions_date_index ON public.positions USING btree (date);
CREATE INDEX IF NOT EXISTS positions_drive_id_date_index ON public.positions USING btree (drive_id, date);
CREATE UNIQUE INDEX IF NOT EXISTS positions_pkey ON public.positions USING btree (id);
CREATE UNIQUE INDEX IF NOT EXISTS schema_migrations_pkey ON public.schema_migrations USING btree (version);
CREATE UNIQUE INDEX IF NOT EXISTS settings_pkey ON public.settings USING btree (id);
CREATE UNIQUE INDEX IF NOT EXISTS "states_car_id__end_date_IS_NULL_index" ON public.states USING btree (car_id, ((end_date IS NULL))) WHERE (end_date IS NULL);
CREATE INDEX IF NOT EXISTS states_car_id_index ON public.states USING btree (car_id);
CREATE UNIQUE INDEX IF NOT EXISTS states_pkey ON public.states USING btree (id);
CREATE UNIQUE INDEX IF NOT EXISTS tokens_pkey ON public.tokens USING btree (id);
CREATE INDEX IF NOT EXISTS updates_car_id_index ON public.updates USING btree (car_id);
CREATE UNIQUE INDEX IF NOT EXISTS updates_pkey ON public.updates USING btree (id);

With a quick look into the migrations, I could not find one for positions_date_index for example.

I assume, this one should normally create the index positions_date_index (introduced by https://github.com/teslamate-org/teslamate/pull/3186): https://github.com/teslamate-org/teslamate/blob/363e5a661612a090cfac5bf89715f7e9de39f420/priv/repo/migrations/20230417225712_composite_index_to_position.exs

Type of installation

Docker

Version

v1.30.1

swiffer commented 2 days ago

@JakobLichterfeld - the index on positions date has been created 2019

-> https://github.com/teslamate-org/teslamate/blob/363e5a661612a090cfac5bf89715f7e9de39f420/priv/repo/migrations/20190416125429_add_indexes_on_dates.exs#L5

i'm currently counting 13 pkey indexes and 23 indexes created via migrations in my instance. i compared to the migrations in both directions confirming the assumption of 36 indexes so far.

pg_indexes_202409161014.xlsx