Re: query - laziness of lateral join with function

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: query - laziness of lateral join with function
Дата
Msg-id 23205.1423775861@sss.pgh.pa.us
обсуждение исходный текст
Ответ на query - laziness of lateral join with function  (paulcc <paulcc.two@gmail.com>)
Ответы Re: query - laziness of lateral join with function  (David G Johnston <david.g.johnston@gmail.com>)
Re: query - laziness of lateral join with function  (Paul Callaghan <paulcc.two@gmail.com>)
Список pgsql-performance
paulcc <paulcc.two@gmail.com> writes:
>    select count(alpha.id)
>    from alpha
>    cross join lateral some_function(alpha.id) as some_val
>    where alpha.test

>    Here the function is strict, and moreover its argument will never
>    be null - hence there should always be a non-null value returned.

>    I would expect that since the function doesn't impact on the
>    number of rows (always one value returned for each row in alpha),
>    then I'd hope the function is never called. EXPLAIN shows it being
>    called for each row in the main table.

You're out of luck on that one at the moment, although testing it on
HEAD suggests that commit 55d5b3c08279b487cfa44d4b6e6eea67a0af89e4
might have fixed it for you in future releases.

>    select alpha.*, some_val
>    from alpha
>    cross join lateral some_function(alpha.id) as some_val
>    where alpha.test
>    order by alpha.name asc
>    limit 100 offset 100

>    Same setup as above, and I'd expect that the ordering and
>    selection of rows can be done first and the function only
>    called on the rows that get selected.

The planner might produce such a result if there's an opportunity
to perform the sorting via an index on "alpha" (ie, the ORDER BY
matches some index).  If it has to do an explicit sort it's gonna
do the join first.

(If you have such an index, and it's not going for the plan you want,
you might need to crank up the COST property of some_function to
persuade the planner that it should try to minimize the number of calls
even if that means a slower scan choice.)

In both cases though, I rather wonder why you're using LATERAL at all, as
opposed to just calling the function in the main query when you want its
result.  The query planner can't be expected to make up for arbitrary
amounts of stupidity in the formulation of the submitted query.

            regards, tom lane


В списке pgsql-performance по дате отправления:

Предыдущее
От: paulcc
Дата:
Сообщение: query - laziness of lateral join with function
Следующее
От: David G Johnston
Дата:
Сообщение: Re: query - laziness of lateral join with function