Thread: pgsql: Fix LATERAL join test in test memoize.sql
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(-)
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
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
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