pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql: Fix LATERAL join test in test memoize.sql
6+ messages / 3 participants
[nested] [flat]
* pgsql: Fix LATERAL join test in test memoize.sql
@ 2023-01-23 23:29 David Rowley <drowley@postgresql.org>
0 siblings, 0 replies; 6+ messages in thread
From: David Rowley @ 2023-01-23 23:29 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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
------
REL_14_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/bcec08907e9357425ec0b3ea9ce03fd41fa6b8de
Modified Files
--------------
src/test/regress/expected/memoize.out | 8 +++++---
src/test/regress/sql/memoize.sql | 6 ++++--
2 files changed, 9 insertions(+), 5 deletions(-)
^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix LATERAL join test in test memoize.sql
@ 2023-01-23 23:30 David Rowley <drowley@postgresql.org>
0 siblings, 0 replies; 6+ messages in thread
From: David Rowley @ 2023-01-23 23:30 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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
------
REL_15_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/73f77ab508d1514ca604c47b6f56c5dee4dd9025
Modified Files
--------------
src/test/regress/expected/memoize.out | 8 +++++---
src/test/regress/sql/memoize.sql | 6 ++++--
2 files changed, 9 insertions(+), 5 deletions(-)
^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix LATERAL join test in test memoize.sql
@ 2023-01-23 23:30 David Rowley <drowley@postgresql.org>
2023-01-23 23:37 ` Re: pgsql: Fix LATERAL join test in test memoize.sql Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: David Rowley @ 2023-01-23 23:30 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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(-)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Fix LATERAL join test in test memoize.sql
2023-01-23 23:30 pgsql: Fix LATERAL join test in test memoize.sql David Rowley <drowley@postgresql.org>
@ 2023-01-23 23:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-24 00:08 ` Re: pgsql: Fix LATERAL join test in test memoize.sql David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2023-01-23 23:37 UTC (permalink / raw)
To: David Rowley <drowley@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org, Richard Guo <guofenglinux@gmail.com>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Fix LATERAL join test in test memoize.sql
2023-01-23 23:30 pgsql: Fix LATERAL join test in test memoize.sql David Rowley <drowley@postgresql.org>
2023-01-23 23:37 ` Re: pgsql: Fix LATERAL join test in test memoize.sql Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-01-24 00:08 ` David Rowley <dgrowleyml@gmail.com>
2023-01-24 00:23 ` Re: pgsql: Fix LATERAL join test in test memoize.sql Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: David Rowley @ 2023-01-24 00:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Rowley <drowley@postgresql.org>; pgsql-committers@lists.postgresql.org, Richard Guo <guofenglinux@gmail.com>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql: Fix LATERAL join test in test memoize.sql
2023-01-23 23:30 pgsql: Fix LATERAL join test in test memoize.sql David Rowley <drowley@postgresql.org>
2023-01-23 23:37 ` Re: pgsql: Fix LATERAL join test in test memoize.sql Tom Lane <tgl@sss.pgh.pa.us>
2023-01-24 00:08 ` Re: pgsql: Fix LATERAL join test in test memoize.sql David Rowley <dgrowleyml@gmail.com>
@ 2023-01-24 00:23 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2023-01-24 00:23 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-committers@lists.postgresql.org, Richard Guo <guofenglinux@gmail.com>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-01-24 00:23 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-23 23:29 pgsql: Fix LATERAL join test in test memoize.sql David Rowley <drowley@postgresql.org>
2023-01-23 23:30 pgsql: Fix LATERAL join test in test memoize.sql David Rowley <drowley@postgresql.org>
2023-01-23 23:30 pgsql: Fix LATERAL join test in test memoize.sql David Rowley <drowley@postgresql.org>
2023-01-23 23:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-24 00:08 ` David Rowley <dgrowleyml@gmail.com>
2023-01-24 00:23 ` 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