From: paulcc <paulcc.two@gmail.com>
To: pgsql-performance@postgresql.org
Subject: query - laziness of lateral join with function
Date: Thu, 12 Feb 2015 11:07:09 -0700 (MST)
Message-ID: <1423764429139-5837706.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi
I'm using cross join lateral with a non-trivial function in
an attempt to limit calculation of that function, and am
wondering about some aspects of how lateral is currently
implemented.
NB these queries are generated by a certain ORM, and are
usually embedded in much more complex queries...
Case one: counting
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.
Case two: pagination
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. Again, EXPLAIN shows
otherwise.
So: am I expecting too much for LATERAL, or have I missed a
trick somewhere?
Many thanks in advance!
Paul
--
View this message in context: http://postgresql.nabble.com/query-laziness-of-lateral-join-with-function-tp5837706.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: paulcc.two@gmail.com
Subject: Re: query - laziness of lateral join with function
In-Reply-To: <1423764429139-5837706.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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