materialized view order by and clustering

From: Rick Otten <rottenwindfish(at)gmail(dot)com>
To: "pgsql-performa(dot)" <pgsql-performance(at)postgresql(dot)org>
Subject: materialized view order by and clustering
Date: 2016-11-17 16:36:21
Message-ID: CAMAYy4LnKi-Bc-0Nxq1kVvL31=ntwKhqG=-j6r-v9t20auDmsg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message David G. Johnston 2016-11-17 17:06:43 Re: materialized view order by and clustering
Previous Message Seckin Pulatkan 2016-11-17 12:33:06 Re: Query planner chooses index scan backward instead of better index option