Re: Rules documentation example

Paul A Jungwirth <pj(at)illuminatedcomputing(dot)com> writes:
> I'm reading the docs about the Postgres Rule system here:
> https://www.postgresql.org/docs/12/rules-views.html
> That page says:

>> It turns out that the planner will collapse this tree into a two-level query tree: the bottommost SELECT commands will be “pulled up” into the middle SELECT since there's no need to process them separately. But the middle SELECT will remain separate from the top, because it contains aggregate functions. If we pulled those up it would change the behavior of the topmost SELECT, which we don't want.

> But I don't see an aggregate function. Is it referring to MIN?

Perhaps. Digging in the git history, that text seems to be mine
(commit 1045304a3), but the example that it's talking about was
pre-existing. I think I might've just misread it. It's also
likely (assuming that I was documenting a behavior that I actually
saw at the time) that the real issue is that MIN(), as presented,
defaults to being volatile which would also prevent such flattening.
But this example is so old that I'm not sure whether that particular
optimization behavior existed then.

I'm inclined to:

(1) get rid of the example's MIN() function in favor of using
LEAST(), which is standard and less confusing;

(2) change the text to just say that the planner flattens these
subqueries, so we don't pay any execution-time penalty from the
way the view replacements are handled.

regards, tom lane