From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: Maciek Sakrejda <m.sakrejda@gmail.com>
Cc: pgsql-docs@postgresql.org
Subject: Re: ORDER BY in materialized view example?
Date: Tue, 23 Nov 2021 12:44:56 -0500
Message-ID: <38996.1637689496@sss.pgh.pa.us> (raw)
In-Reply-To: <1e9f99f9-9cdb-4bc3-85d9-c017fbc54566@enterprisedb.com>
References: <CAOtHd0D-OvrUU0C=4hX28p4BaSE1XL78BAQ0VcDaLLt8tdUzsg@mail.gmail.com>
<1e9f99f9-9cdb-4bc3-85d9-c017fbc54566@enterprisedb.com>
Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> On 23.11.21 07:18, Maciek Sakrejda wrote:
>> An example in the materialized view documentation [1] includes an ORDER
>> BY clause without a clear reason. Does it help build the index more
>> efficiently? I suppose it's also sort of like a CLUSTER?
> I agree the ORDER BY is not relevant to the example. There might be
> some implementation-dependent advantage to ordering a materialized view,
> but if there is, it isn't explained in the example.
Yeah. It would result in the initial contents of the matview being
ordered, but I'm sure we don't wish to guarantee that REFRESH would
preserve that. I'm on board with just removing the ORDER BY from
that example.
There is also this rather opaque "note" in the REFRESH MATERIALIZED VIEW
man page:
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'd rather say something like
If there is an ORDER BY clause in the matview's defining query,
the original contents of the matview will be ordered that way;
but REFRESH MATERIALIZED VIEW does not guarantee to preserve
that ordering.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: tgl@sss.pgh.pa.us, peter.eisentraut@enterprisedb.com, m.sakrejda@gmail.com
Subject: Re: ORDER BY in materialized view example?
In-Reply-To: <38996.1637689496@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox