Thread: materialized view order by and clustering
If I construct the materialized view with an 'order by', I can use a BRIN index to a sometimes significant performance advantage, at least for the primary sort field. I have observed that even though the first pass is a little lossy and I get index rechecks, it is still much faster than a regular btree index.
Does it matter if I also try to CLUSTER the materialized view on that primary sort field? Or is it already clustered because of the 'order by'?
Would the brin index work better on a clustered materialized view instead of an ordered materialized view?
When I refresh the materialized view (concurrently) is the order_by preserved? Would the clustering be preserved?
I'm trying to get a handle on the concept of clustering and how that is different than order_by and which would be better and how much advantage it really gets me. I'll continue to do experiments with this, but thought some of the performance gurus on this list would have some immediate thoughts on the subject off the top of their heads, and others reading this list might find the observations interesting.
Thank you for your time.
Does it matter if I also try to CLUSTER the materialized view on that primary sort field? Or is it already clustered because of the 'order by'?
[...]When I refresh the materialized view (concurrently) is the order_by preserved? Would the clustering be preserved?
The notes on the REFRESH MATERIALIZED VIEW page seem informative to this question:"While the default index for future CLUSTER operations is retained, REFRESH MATERIALIZED VIEW does not order the generated rows based on this property. If you want the data to be ordered upon generation, you must use an ORDER BY clause in the backing query."
I'm trying to get a handle on the concept of clustering and how that is different than order_by and which would be better and how much advantage it really gets me.
CLUSTER is a physical property (table only)
while ORDER BY is a logical one (view only)
With respect to materialized views - which act as both table and view - the logically ordered view data gets saved to the physical table thus making the table clustered on whatever order by is specified.
David J.