Re: Avoiding bad prepared-statement plans.
| От | Bart Samwel |
|---|---|
| Тема | Re: Avoiding bad prepared-statement plans. |
| Дата | |
| Msg-id | ded01eb21002110439w593e540cod112c52c15cb6e4d@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Avoiding bad prepared-statement plans. (Pavel Stehule <pavel.stehule@gmail.com>) |
| Ответы |
Re: Avoiding bad prepared-statement plans.
|
| Список | pgsql-hackers |
On Thu, Feb 11, 2010 at 13:25, Pavel Stehule <pavel.stehule@gmail.com> wrote:
I guess the required complexity depends on how variable planning costs are. If planning is typically <= 2 ms, then a hard limit on estimated price is useful and can be set as low as (the equivalent of) 15 ms. However, if planning costs can be 50 ms, then the lowest reasonable "fixed" limit is quite a bit larger than that -- and that does not solve the problem reported earlier in this thread, where a query takes 30 ms using a generic plan and 1 ms using a specialized plan.
Anyhow, I have no clue how much time the planner takes. Can anybody provide any statistics in that regard?
Cheers,
Bart
2010/2/11 Bart Samwel <bart@samwel.tk>:It could be a good idea. I don't belive to sophisticate methods. There> Perhaps this could be based on a (configurable?) ratio of observed planning
> time and projected execution time. I mean, if planning it the first time
> took 30 ms and projected execution time is 1 ms, then by all means NEVER
> re-plan. But if planning the first time took 1 ms and resulted in a
> projected execution time of 50 ms, then it's relatively cheap to re-plan
> every time (cost increase per execution is 1/50 = 2%), and the potential
> gains are much greater (taking a chunk out of 50 ms adds up quickly).
can be a very simply solution. The could be a "limit" for price. More
expensive queries can be replaned every time when the price will be
over limit.
I guess the required complexity depends on how variable planning costs are. If planning is typically <= 2 ms, then a hard limit on estimated price is useful and can be set as low as (the equivalent of) 15 ms. However, if planning costs can be 50 ms, then the lowest reasonable "fixed" limit is quite a bit larger than that -- and that does not solve the problem reported earlier in this thread, where a query takes 30 ms using a generic plan and 1 ms using a specialized plan.
Anyhow, I have no clue how much time the planner takes. Can anybody provide any statistics in that regard?
Cheers,
Bart
В списке pgsql-hackers по дате отправления: