Обсуждение: pgsql: Fix LATERAL join test in test memoize.sql

Поиск
Список
Период
Сортировка

pgsql: Fix LATERAL join test in test memoize.sql

От
David Rowley
Дата:
Fix LATERAL join test in test memoize.sql

The test in question was meant to be testing Memoize to ensure it worked
correctly when the inner side of the join contained lateral vars, however,
nothing in the lateral subquery stopped it from being pulled up into the
main query, so the planner did that, and that meant no more lateral vars.

Here we add a simple ORDER BY to stop the planner from being able to
pullup the lateral subquery.

Author: Richard Guo
Discussion: https://postgr.es/m/CAMbWs4_LHJaN4L-tXpKMiPFnsCJWU1P8Xh59o0W7AA6UN99=cQ@mail.gmail.com
Backpatch-through: 14, where Memoize was added.

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/b762fed6481e722a022ac2bea02151e080c31fa6

Modified Files
--------------
src/test/regress/expected/memoize.out | 8 +++++---
src/test/regress/sql/memoize.sql      | 6 ++++--
2 files changed, 9 insertions(+), 5 deletions(-)


Re: pgsql: Fix LATERAL join test in test memoize.sql

От
Tom Lane
Дата:
David Rowley <drowley@postgresql.org> writes:
> Here we add a simple ORDER BY to stop the planner from being able to
> pullup the lateral subquery.

Hmmm ... we have an agreed syntax for a subquery optimization fence,
and that ain't it.  I wouldn't count on this not breaking again in
the future.  Why not OFFSET 0, which is the usual method?

            regards, tom lane



Re: pgsql: Fix LATERAL join test in test memoize.sql

От
David Rowley
Дата:
On Tue, 24 Jan 2023 at 12:37, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hmmm ... we have an agreed syntax for a subquery optimization fence,
> and that ain't it.  I wouldn't count on this not breaking again in
> the future.  Why not OFFSET 0, which is the usual method?

You're right that OFFSET 0 would have been a better choice. I just
wasn't aware that we were considering changing the code so we pull up
subqueries with an ORDER BY. In fact, I thought there were several
good reasons we're not doing that. e.g. allow aggregation in outer
query by some order specified by the subquery. But perhaps any changes
we make in this area would be more conditional than just ignoring the
fact that the subquery has an ORDER BY.

In any case, I agree that OFFSET 0 is a better choice, so I'll go and edit that.

David



Re: pgsql: Fix LATERAL join test in test memoize.sql

От
Tom Lane
Дата:
David Rowley <dgrowleyml@gmail.com> writes:
> You're right that OFFSET 0 would have been a better choice. I just
> wasn't aware that we were considering changing the code so we pull up
> subqueries with an ORDER BY.

No such plan is in the offing AFAIK, but it doesn't seem entirely
out of the question either.

> In fact, I thought there were several
> good reasons we're not doing that. e.g. allow aggregation in outer
> query by some order specified by the subquery. But perhaps any changes
> we make in this area would be more conditional than just ignoring the
> fact that the subquery has an ORDER BY.

Yeah, we've historically honored subquery ORDER BY and I doubt we'd
want to stop.  But I'm not sure that that that completely precludes
subquery flattening, especially for single-relation subqueries such
as these.  You could imagine, for example, still doing the pull-up
and then only accepting paths for the rel that satisfy the ORDER BY.
This could still lead to better optimization of the outer query
than what happens now, I think.

Anyway, it's not something I expect to happen soon, but maybe
someday.

            regards, tom lane