Subject: Re: Skip Orderby Execution for Materialized Views

From: "David G. Johnston" <[email-masked]>
Date: Sun, 1 Oct 2023 09:05:49 -0700

On Sun, Oct 1, 2023 at 8:57 AM Zhang Mingli <[email-masked]> wrote:

> And if it’s true, shall we skip the order by clause for Materialized
> View  when executing create/refresh statement?
>

We tend to do precisely what the user writes into their query.  If they
don't want an order by they can remove it.  I don't see any particular
reason we should be second-guessing them here. And what makes the trade-off
worse is the reasonable expectation that we'd provide a way to force an
ordering of the inserts should the user actually want it after we defaulted
to ignoring that part of their query.

But yes, you are correct that adding an order by to a materialized view is
typically pointless.  To the extent it is detrimental varies since even
partially ordered results can save on processing time.

David J.