perf: check migrations for indexes, add if necessary

[JakobLichterfeld]

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]

@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

[swiffer]

@JakobLichterfeld - small reminder on this one - with all the perf improvements making it into the next release it would be great to mention this issue in the release notes once.

or place this somewhere in the docs (under troubleshooting?) but hard to keep up to date in that case...

[JakobLichterfeld]

@JakobLichterfeld - small reminder on this one - with all the perf improvements making it into the next release it would be great to mention this issue in the release notes once.

Yeah it is on my list, thanks for reminder.