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.