From: Jonathan S. Katz <jkatz@postgresql.org>
To: 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 13:11:08 -0500
Message-ID: <0db5b15e-ce75-f572-2686-b6209084c8e6@postgresql.org> (raw)
In-Reply-To: <38996.1637689496@sss.pgh.pa.us>
References: <CAOtHd0D-OvrUU0C=4hX28p4BaSE1XL78BAQ0VcDaLLt8tdUzsg@mail.gmail.com>
<1e9f99f9-9cdb-4bc3-85d9-c017fbc54566@enterprisedb.com>
<38996.1637689496@sss.pgh.pa.us>
On 11/23/21 12:44 PM, Tom Lane wrote:
> 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.
+1
> 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.
+1. I think I got bit by this in the real world years back. The above
comment is pretty clear.
Thanks,
Jonathan
Attachments:
[application/pgp-signature] OpenPGP_signature (839B, ../0db5b15e-ce75-f572-2686-b6209084c8e6@postgresql.org/2-OpenPGP_signature)
download
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: jkatz@postgresql.org, tgl@sss.pgh.pa.us, peter.eisentraut@enterprisedb.com, m.sakrejda@gmail.com
Subject: Re: ORDER BY in materialized view example?
In-Reply-To: <0db5b15e-ce75-f572-2686-b6209084c8e6@postgresql.org>
* 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