From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: Svenne Krap <svenne@krap.dk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems with multiple layers of functions
Date: Fri, 24 Mar 2006 11:02:34 -0500
Message-ID: <5090.1143216154@sss.pgh.pa.us> (raw)
In-Reply-To: <20060324125919.GD90527@pervasive.com>
References: <4423EACD.80706@krap.dk>
<20060324125919.GD90527@pervasive.com>
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Fri, Mar 24, 2006 at 01:49:17PM +0100, Svenne Krap wrote:
>> explain select dataset_id, entity, sum(amount) from entrydata_current
>> where flow_direction in (select * from outflow_direction(dataset_id))
>> and dataset_id in (select * from get_dataset_ids(122)) group by
>> dataset_id, entity;
> The issue is that the planner has no way to know what's comming back
> from get_dataset_ids.
More specifically, the first IN is not optimizable into a join because
the results of the sub-SELECT depend on the current row of the outer
query. The second IN is being optimized fine, but the first one is
what's killing you.
I'd suggest refactoring the functions into something that returns a set
of outflow_direction/dataset_id pairs, and then phrase the query as
where (flow_direction, dataset_id) in (select * from new_func(122))
You could do it without refactoring:
where (flow_direction, dataset_id) in
(select outflow_direction(id),id from get_dataset_ids(122) id)
however this won't work if outflow_direction() is a plpgsql function
because of limitations in plpgsql's set-function support. (It will work
if outflow_direction() is a SQL function, or you could kluge it as a SQL
function wrapper around a plpgsql function.)
regards, tom lane
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, jnasby@pervasive.com, svenne@krap.dk
Subject: Re: Performance problems with multiple layers of functions
In-Reply-To: <5090.1143216154@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