I looked into this a bit and here are some different options I see for us:

• If we want to keep the crashes_list_view around, we need to optimize it.
  • This view is an example of "views on views on views". It queries the crashes_injury_metrics_view which queries person_injury_metrics_view. I tested getting rid of person_injury_metrics_view and instead using generated columns on the people table for most of those cols which works well, takes like 25% of the OG query time. but there are a few cols that the person_injury_metrics_view creates like cris_fatal_injury which cant be a computed col bc it requires data from the people_cris table, same with all the units injuries in that view like motor_vehicle_fatal_injry.
  • Get rid of the to_char date/time operations in crashes_list_view and instead have those calculations happen in computed columns in the crashes table? Probably wont make a big diff in query time
• We could make a move to materialized views instead.
  • Use triggers to refresh the materialized views whenever inserts/edits are made.
  • May not be tenable bc refreshing a view takes timeee so what about the cris import? Or concurrent edits?
• We make use of generated columns and triggers as much as possible to get rid of the crashes_list_view in entirety and have the list view page query from the crashes page.
  • Means we need to maintain more triggers but boy will our queries be speedy. Should also use generated columns everywhere possible to cut down on triggers, so like a whole bunch of whats in the people_injury_metrics_view could be made into generated/computed cols.
  • Or could get rid of the crashes_list_view but keep around crashes_injury_metrics_view and just query the crashes table which has a relationship with it and in the front end we will be querying the columns we need from the view thru the hasura relationship?

I think we could experiment with getting rid of crashes_list_view and maybe the people_injury_metrics_view and make more use of generated cols, and use the crashes table and its relationship w crashes_injury_metrics_view to query for the list page. Trying to balance speed with also not having to manage too many triggers.