Re: [PERFORM] materialized view order by and clustering

[David G. Johnston]

On Thu, Nov 17, 2016 at 9:36 AM, Rick Otten 
wrote:

>
> 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."

​https://www.postgresql.org/docs/9.6/static/sql-refreshmaterializedview.html
​
​


> 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.
​
​

[PERFORM] materialized view order by and clustering

[Rick Otten]

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.