From: Tom Lane <tgl@sss.pgh.pa.us>
To: paulcc <paulcc.two@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: query - laziness of lateral join with function
Date: Thu, 12 Feb 2015 16:17:41 -0500
Message-ID: <23205.1423775861@sss.pgh.pa.us> (raw)
In-Reply-To: <1423764429139-5837706.post@n5.nabble.com>
References: <1423764429139-5837706.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
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: tgl@sss.pgh.pa.us, paulcc.two@gmail.com
Subject: Re: query - laziness of lateral join with function
In-Reply-To: <23205.1423775861@sss.pgh.pa.us>
* 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