missing INDEX on database

[FabienD74]

Hi, I have run the script found here ( https://github.com/pgexperts/pgx_scripts/blob/master/indexes/fk_no_index.sql )

A) The report listed a few Missing indexes on "Foreign keys"

table stop_times : feed_id + stop_id table trips: feed_id + service_id table trips: feed_id + route_id

Those indexes should speed up navigation between tables

B) furthermore: Some indexes on dates in tables calendar and calendar_dates ? Index on Latitude + Longitude on stops ?

I create all (A+ B) on my side... Searching departudes arround phone GPS : 300ms (down from 2-3 seconds)

Should be tested on sqlite, but works fine on postgresql.

PS: Some indexes are already create at runtime. Have a nice day

[vingerha]

On A: table stop_times : feed_id + stop_id table trips: feed_id + service_id table trips: feed_id + route_id In none of the sql that I use is the feed_id involved (as the db is always from 1 feed), so no sense to add that (yet)

On B: None of the queries search for actual values in lat/lon, only boundaries in which the lat/lon falls, so a normal index makes no sense. Maybe a b-tree may help but this may add to the size too. I would also need to know the query times just to see if it adds substantially , i.e. I will not add anything if it goes from 1s to 0.9...that may be 10% but it has no effect on the end user perception

[FabienD74]

i have old version runnin on sqlite : get_local_stops_next_departures_old: In 2228ms new version using postgres+new index + new logic :

`2024-06-03 14:20:30.347 DEBUG (MainThread) [custom_components.gtfs2_pg.gtfs2_pg_sensor_1] next_departures_postgresql BEGIN:                                                                    
2024-06-03 14:20:30.477 DEBUG (MainThread) [custom_components.gtfs2_pg.gtfs2_pg_sensor_1] CASE 1A: date: 2024-06-02 38:20 -> 40:20:   0 rows                                                   
2024-06-03 14:20:30.549 DEBUG (MainThread) [custom_components.gtfs2_pg.gtfs2_pg_sensor_1] CASE 1B: date: 2024-06-03 14:20 -> 16:20:   585 rows                                                 
2024-06-03 14:20:30.616 INFO (MainThread) [custom_components.gtfs2_pg.gtfs2_pg_sensor_1] next_departures_postgresql END: In 269ms Processed 58 stops 585 departures. Returned 585 departures

`

[vingerha]

Removed the other message, the above lines, are these using the existing queries or queries already adapted for your personal case?

EDIT: forget my question on new queries, so this is new on new, which I cannot verify quickly. I will have to run a sql query later this week to see if a b-tree index would make a diff

[vingerha]

Closing this as the proposed indexes do no apply to the current setup of gtfs2, neither are there complaints about performance. Not spending time to get this from already OK to more than OK