To: "Edward Di Geronimo Jr." <edigeronimo@xtracards.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance issues with custom functions 
In-reply-to: <435FE820.4070701@xtracards.com> 
References: <435FE820.4070701@xtracards.com>
Comments: In-reply-to "Edward Di Geronimo Jr." <edigeronimo@xtracards.com>
	message dated "Wed, 26 Oct 2005 16:33:36 -0400"
Date: Wed, 26 Oct 2005 19:18:37 -0400
Message-ID: <12437.1130368717@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Edward Di Geronimo Jr." <edigeronimo@xtracards.com> writes:
> ... I'd like to know exactly what causes 
> the bottleneck in the original query, and if there are other approaches 
> to solving the issue in case I need them in future queries.

This is fairly hard to read ... it would help a lot if you had shown the
view definitions that the query relies on, so that we could match up the
plan elements with the query a bit better.

However, I'm thinking the problem is with this IN clause:

>         where pl.network_id in (select ns.network_id
>                         from development.network_state ns
>                           where ns.from_date < current_time
>                             and (ns.thru_date > current_time or 
> ns.thru_date is null)
>                         and (ns.state_cd = pl.state_cd or ns.state_cd='')
>                           )

Because the sub-SELECT references pl.state_cd (an outer variable
reference), there's no chance of optimizing this into a join-style IN.
So the sub-SELECT has to be re-executed for each row of the outer query.

BTW, it's not apparent to me that your "flattened" query gives the same
answers as the original.  What if a pl row can join to more than one
row of the ns output?

			regards, tom lane