To: "Jim C. Nasby" <jnasby@pervasive.com>
cc: Svenne Krap <svenne@krap.dk>, pgsql-performance@postgresql.org
Subject: Re: Performance problems with multiple layers of functions 
In-reply-to: <20060324125919.GD90527@pervasive.com> 
References: <4423EACD.80706@krap.dk> <20060324125919.GD90527@pervasive.com>
Comments: In-reply-to "Jim C. Nasby" <jnasby@pervasive.com>
	message dated "Fri, 24 Mar 2006 06:59:19 -0600"
Date: Fri, 24 Mar 2006 11:02:34 -0500
Message-ID: <5090.1143216154@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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