Re: Postgres8.0 planner chooses WRONG plan

От: Tom Lane
Тема: Re: Postgres8.0 planner chooses WRONG plan
Дата: ,
Msg-id: 13187.1136995638@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Postgres8.0 planner chooses WRONG plan  (Pallav Kalva)
Ответы: Re: Postgres8.0 planner chooses WRONG plan  (Pallav Kalva)
Список: pgsql-performance

Скрыть дерево обсуждения

Postgres8.0 planner chooses WRONG plan  (Pallav Kalva, )
 Re: Postgres8.0 planner chooses WRONG plan  (Tom Lane, )
  Re: Postgres8.0 planner chooses WRONG plan  (Pallav Kalva, )
   Re: Postgres8.0 planner chooses WRONG plan  ("Jim C. Nasby", )

Pallav Kalva <> 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


В списке pgsql-performance по дате сообщения:

От: "Dave Dutcher"
Дата:
Сообщение: Showing Column Statistics Number
От: Michael Fuhr
Дата:
Сообщение: Re: Showing Column Statistics Number