# Add support for materialized view
**URL:** https://discuss.tryton.org/t/add-support-for-materialized-view/110
**Category:** Feature
**Created:** [April 7, 2016, 11:16am UTC](https://discuss.tryton.org/t/add-support-for-materialized-view/110 "2016-04-07T11:16:20Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [April 7, 2016, 11:16am UTC](https://discuss.tryton.org/t/add-support-for-materialized-view/110/1 "2016-04-07T11:16:20Z")
## 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](https://www.odoo.com/groups/community-framework-62/community-framework-17708863?mode=thread&date_begin=&date_end=) 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.
---
### Author:  [@nicoe](https://discuss.tryton.org/u/nicoe)
#### Post date: [April 7, 2016, 11:46am UTC](https://discuss.tryton.org/t/add-support-for-materialized-view/110/2 "2016-04-07T11:46:35Z")
> [@ced](#):
>
> And it should also have an option to refresh concurrently or not.
I don’t understand this part. What do you mean by “refresh concurrently or not”?
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [April 7, 2016, 12:36pm UTC](https://discuss.tryton.org/t/add-support-for-materialized-view/110/3 "2016-04-07T12:36:53Z")
This is a [parameter](http://www.postgresql.org/docs/current/static/sql-refreshmaterializedview.html) of the `REFRESH MATERIALIZED VIEW` that we can not really guess because there are pros and cons for its usage.
---
### Author:  [@pokoli](https://discuss.tryton.org/u/pokoli)
#### Post date: [April 8, 2016, 10:21am UTC](https://discuss.tryton.org/t/add-support-for-materialized-view/110/4 "2016-04-08T10:21:44Z")
We have developed some complex table\_queries that will benefit of this behaviour, so I it will be a very welcomed feature.