On Thu, Nov 17, 2016 at 9:36 AM, Rick Otten <rottenwindf...@gmail.com> 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.
