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

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

Improve LATERAL join case in test memoize.sql

От
Richard Guo
Дата:
I happened to notice we have the case in memoize.sql that tests for
memoize node with LATERAL joins, which is

-- Try with LATERAL joins
SELECT explain_memoize('
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2
WHERE t1.unique1 < 1000;', false);

ISTM this is not the right query for the test.  After the subquery being
pulled up into the parent query, there will be no lateral references any
more.  I'm thinking maybe we can add an ORDER BY clause in the subquery
to prevent it from being pulled up.

-- Try with LATERAL joins
SELECT explain_memoize('
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1 ORDER BY 1) t2
WHERE t1.unique1 < 1000;', false);

Attach a trivial patch for the change.

Thanks
Richard
Вложения

Re: Improve LATERAL join case in test memoize.sql

От
David Rowley
Дата:
On Mon, 16 Jan 2023 at 22:27, Richard Guo <guofenglinux@gmail.com> wrote:
> I happened to notice we have the case in memoize.sql that tests for
> memoize node with LATERAL joins, which is
>
> -- Try with LATERAL joins
> SELECT explain_memoize('
> SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
> LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2
> WHERE t1.unique1 < 1000;', false);
>
> ISTM this is not the right query for the test.

> Attach a trivial patch for the change.

Good catch.  I've applied this back to v14.

David