From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pallav Kalva <pkalva@livedatagroup.com>
Cc: PGPerformance <pgsql-performance@postgresql.org>
Subject: Re: Postgres8.0 planner chooses WRONG plan
Date: Wed, 11 Jan 2006 11:07:18 -0500
Message-ID: <13187.1136995638@sss.pgh.pa.us> (raw)
In-Reply-To: <43C523EB.3000201@livedatagroup.com>
References: <43C523EB.3000201@livedatagroup.com>
Pallav Kalva <pkalva@livedatagroup.com> writes:
> I am having problem optimizing this query,
Get rid of the un-optimizable function inside the view. You've
converted something that should be a join into an unreasonably large
number of function calls.
> -> Seq Scan on serviceinstance si
> (cost=0.00..15831.52 rows=7 width=78) (actual time=174.430..18948.210
> rows=358 loops=1)
> Filter: (((subplan) = 'FL'::text) AND
> ((subplan) = '099'::text))
> SubPlan
> -> Result (cost=0.00..0.01 rows=1 width=0)
> (actual time=0.090..0.093 rows=1 loops=3923)
> -> Result (cost=0.00..0.01 rows=1 width=0)
> (actual time=0.058..0.061 rows=1 loops=265617)
The bulk of the cost here is in the second subplan (0.061 * 265617 =
16202.637 msec total runtime), and there's not a darn thing Postgres
can do to improve this because the work is all down inside a "black box"
function. In fact the planner does not even know that the function call
is expensive, else it would have preferred a plan that requires fewer
evaluations of the function. The alternative plan you show is *not*
faster "because it's an indexscan"; it's faster because get_parametervalue
is evaluated fewer times.
The useless sub-SELECTs atop the function calls are adding their own
little increment of wasted time, too. I'm not sure how bad that is
relative to the function calls, but it's certainly not helping.
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, pkalva@livedatagroup.com
Subject: Re: Postgres8.0 planner chooses WRONG plan
In-Reply-To: <13187.1136995638@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