Rational
A common pattern in Tryton is to use table_query to create a Model for report/statistic purpose. This works very well and it is very flexible because the query can be customized by the context.
But due to the communication protocol between the client and the server, we need to have an ID on every ModelStorage. Most of the time, it is quite simple to generate one but sometime it is complex. In such case the result SQL query can not be very optimized by the database and it result of a query plan that compute the all tuples for the table_query which are filtered after. This was exposed on the Odoo mailing list by Alexandre Fayolle.
Tryton has already the advantage to use a sub-query instead of view which in some way create a barrier that prevent the query planner to make some optimisation. But any way, if we have a complex ID generation, a solution is to use materialized view with indexes (on back-end that support it).
Proposal
We could have a Mixin which will use the query from table_query to create a materialized view (on supported back-end) and once the view is created it is returned by table_query instead of the query.
The Mixin should also have a method to refresh the view that could be called by a cron task, a wizard, a hook on other Models or even by the Model after a time-out. And it should also have an option to refresh concurrently or not.
Of course for back-end that doesn’t support materialized view, this should be disabled and the standard table_query behaviour should be run.
Also the TableHandler should allow to create index on the materialized view so the select on fields will be used (probably a test if table_query returns a Table or a Query).
The __register__ should recreate the view on each call to ensure the query is still synchronized.
A limitation is that such materialized view could not depend on the context.