Skip site navigation (1) Skip section navigation (2)

Re: Performance problems with multiple layers of functions

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Svenne Krap <svenne(at)krap(dot)dk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance problems with multiple layers of functions
Date: 2006-03-24 12:59:19
Message-ID: 20060324125919.GD90527@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-performance
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;
<snip> 
> which does not return within 10 minutes - which is unacceptable.


The issue is that the planner has no way to know what's comming back
from get_dataset_ids.

I think your best bet will be to wrap that select into it's own function
and have that function prepare the query statement, going back to
hard-coded values. So you could do something like:

SQL := 'SELECT ... AND dataset_id IN (''' || get_dataset_ids(122) ||
''');' (yeah, I know that won't work as written, but you get the idea).
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

Responses

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2006-03-24 13:01:21
Subject: Re: WAL logging of SELECT ... INTO command
Previous:From: Jim C. NasbyDate: 2006-03-24 12:52:45
Subject: Re: Array performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group