pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedORDER BY in materialized view example?
6+ messages / 4 participants
[nested] [flat]
* ORDER BY in materialized view example?
@ 2021-11-23 06:18 Maciek Sakrejda <m.sakrejda@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Maciek Sakrejda @ 2021-11-23 06:18 UTC (permalink / raw)
To: pgsql-docs
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY in materialized view example?
@ 2021-11-23 16:06 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Maciek Sakrejda <m.sakrejda@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Peter Eisentraut @ 2021-11-23 16:06 UTC (permalink / raw)
To: Maciek Sakrejda <m.sakrejda@gmail.com>; pgsql-docs
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;
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY in materialized view example?
@ 2021-11-23 17:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2021-11-23 17:44 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; +Cc: Maciek Sakrejda <m.sakrejda@gmail.com>; pgsql-docs
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY in materialized view example?
@ 2021-11-23 18:11 Jonathan S. Katz <jkatz@postgresql.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Jonathan S. Katz @ 2021-11-23 18:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Peter Eisentraut <peter.eisentraut@enterprisedb.com>; +Cc: Maciek Sakrejda <m.sakrejda@gmail.com>; pgsql-docs
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY in materialized view example?
@ 2021-11-29 01:05 Maciek Sakrejda <m.sakrejda@gmail.com>
parent: Jonathan S. Katz <jkatz@postgresql.org>
0 siblings, 1 reply; 6+ messages in thread
From: Maciek Sakrejda @ 2021-11-29 01:05 UTC (permalink / raw)
To: Jonathan S. Katz <jkatz@postgresql.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Peter Eisentraut <peter.eisentraut@enterprisedb.com>; pgsql-docs
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:
> 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:
[text/x-patch] 01-drop-materialized-view-order-by.patch (587B, ../../CAOtHd0CRR95L1qMMsGzy4PDu5T8Z4=zkb_tX5TmyGcfaBOp6Eg@mail.gmail.com/3-01-drop-materialized-view-order-by.patch)
download | inline diff:
commit 45d7ca562daf91f8028335ade2e730bb59d25ba0
Author: Maciek Sakrejda <m.sakrejda@gmail.com>
Date: Sun Nov 28 16:37:05 2021 -0800
Drop confusing ORDER BY in materialized view documentation
diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml
index 6065b1c2a3..4aa4e00e01 100644
--- a/doc/src/sgml/rules.sgml
+++ b/doc/src/sgml/rules.sgml
@@ -961,9 +961,6 @@ CREATE MATERIALIZED VIEW sales_summary AS
FROM invoice
WHERE invoice_date < CURRENT_DATE
GROUP BY
- seller_no,
- invoice_date
- ORDER BY
seller_no,
invoice_date;
[text/x-patch] 02-update-refresh-materialized-view-order-by-note.patch (1.2K, ../../CAOtHd0CRR95L1qMMsGzy4PDu5T8Z4=zkb_tX5TmyGcfaBOp6Eg@mail.gmail.com/4-02-update-refresh-materialized-view-order-by-note.patch)
download | inline diff:
commit bef99e3f6c73fc5a64e46bc5902de5346f298047
Author: Maciek Sakrejda <m.sakrejda@gmail.com>
Date: Sun Nov 28 16:43:42 2021 -0800
Update note on materialized view ORDER BY
diff --git a/doc/src/sgml/ref/refresh_materialized_view.sgml b/doc/src/sgml/ref/refresh_materialized_view.sgml
index 3bf8884447..4aa5558636 100644
--- a/doc/src/sgml/ref/refresh_materialized_view.sgml
+++ b/doc/src/sgml/ref/refresh_materialized_view.sgml
@@ -93,12 +93,10 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="parameter">name</
<title>Notes</title>
<para>
- While the default index for future
- <link linkend="sql-cluster"><command>CLUSTER</command></link>
- operations is retained, <command>REFRESH MATERIALIZED VIEW</command> does not
- order the generated rows based on this property. If you want the data
- to be ordered upon generation, you must use an <literal>ORDER BY</literal>
- clause in the backing query.
+ If there is an <literal>ORDER BY</literal> clause in the materialized view's
+ defining query, the original contents of the materialized view will be ordered
+ that way; but <command>REFRESH MATERIALIZED VIEW</command> does not guarantee
+ to preserve that ordering.
</para>
</refsect1>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY in materialized view example?
@ 2021-11-29 17:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Maciek Sakrejda <m.sakrejda@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2021-11-29 17:14 UTC (permalink / raw)
To: Maciek Sakrejda <m.sakrejda@gmail.com>; +Cc: Jonathan S. Katz <jkatz@postgresql.org>; Peter Eisentraut <peter.eisentraut@enterprisedb.com>; pgsql-docs
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-11-29 17:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-11-23 06:18 ORDER BY in materialized view example? Maciek Sakrejda <m.sakrejda@gmail.com>
2021-11-23 16:06 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
2021-11-23 17:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-11-23 18:11 ` Jonathan S. Katz <jkatz@postgresql.org>
2021-11-29 01:05 ` Maciek Sakrejda <m.sakrejda@gmail.com>
2021-11-29 17:14 ` Tom Lane <tgl@sss.pgh.pa.us>
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