mintable | 🍃 Automate your personal finances | GCP library

Problem outline

I've got a performance problem with a view that is to be expanded to include a value associated to a 'smallest/greatest' value. Doing so more than halves the performance even with my smaller test dataset. The real database/query contains many more tables and joins, but these are all either fairly small tables, or simple FK joins. I've reduced the problem down to what I think is a minimal example, but may have inadvertently made more solutions possible that aren't practical with the real case. Please assume in any solution there are an additional ~10-20 ish tables and joins involved, though all are of the simple kind; e.g. lookup tables for various values, like the names of vehicles and such (the real db is more normalized). For example the 'City' value is not actually in Deliveries, but two JOINs removed, but those JOINS are 1:1, they can only ever return a single-row per source row. So any solution that completely upends the underlying query and thus makes it much more difficult to expand it with a simple join is not a great solution. Assume the following (example!) database tables. All number tables are INTs, all 'ID' columns have a PK, all 'fkX' are a foreign key relation to the named table.

Example Data

Table: Deliveries