ORDER BY in materialized view example?

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?

But it seems like the ORDER BY should either be explained or dropped: as
is, this gives the impression that the ORDER BY can be "embedded" into
the resulting relation and persist to other queries that do not include
an explicit ORDER BY. (I recently ran across this belief, though not
sure if this was due to this example.)

Thoughts?

[1]:
https://www.postgresql.org/docs/current/rules-materializedviews.html
<https://www.postgresql.org/docs/current/rules-materializedviews.html&gt;

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.

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

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

Thanks for the feedback. I only had passing familiarity with materialized
views and I didn't even realize the order would not be preserved. All the
more reason to drop that.

I'm attaching two patches: the first drops the original ORDER BY I e-mailed
about, and the second applies Tom's change to the man page note (verbatim,
though with "materialized view" since the "matview" shorthand doesn't seem
to be used in the docs, and with markup).

On Tue, Nov 23, 2021 at 10:11 AM Jonathan S. Katz <jkatz@postgresql.org>
wrote:

Maciek Sakrejda <m.sakrejda@gmail.com> writes:

I'm attaching two patches: the first drops the original ORDER BY I e-mailed
about, and the second applies Tom's change to the man page note (verbatim,
though with "materialized view" since the "matview" shorthand doesn't seem
to be used in the docs, and with markup).

Pushed, thanks for preparing the patch.

regards, tom lane