From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Edward Di Geronimo Jr(dot)" <edigeronimo(at)xtracards(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Performance issues with custom functions |
Date: | 2005-10-26 23:18:37 |
Message-ID: | 12437.1130368717@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
"Edward Di Geronimo Jr." <edigeronimo(at)xtracards(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Steinar H. Gunderson | 2005-10-26 23:22:19 | Re: Materializing a sequential scan |
Previous Message | Tom Lane | 2005-10-26 23:06:15 | Re: Materializing a sequential scan |