From:
Robert Haas <robertmhaas(at)gmail(dot)com>
To:
Bart Samwel <bart(at)samwel(dot)tk>
Cc:
Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Jeroen Vermeulen <jtv(at)xs4all(dot)nl>, pgsql-hackers(at)postgresql(dot)org
Subject:
Re: Avoiding bad prepared-statement plans.
Date:
2010-02-11 12:41:12
Message-ID:
603c8f071002110441j1669930fs7f653edd0d3b1aa8@mail.gmail.com (view raw or flat )
Thread:
2010-02-09 12:08:54 from Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
2010-02-09 12:59:15 from Yeb Havinga <yebhavinga(at)gmail(dot)com>
2010-02-09 13:46:55 from Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
2010-02-09 14:53:17 from Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
2010-02-10 00:47:18 from Kris Jurka <books(at)ejurka(dot)com>
2010-02-11 03:12:41 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-11 04:07:57 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-09 22:21:43 from Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
2010-02-09 13:50:32 from Richard Huxton <dev(at)archonet(dot)com>
2010-02-09 14:25:19 from Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
2010-02-09 14:45:57 from Richard Huxton <dev(at)archonet(dot)com>
2010-02-09 14:10:22 from Andres Freund <andres(at)anarazel(dot)de>
2010-02-09 14:28:01 from Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
2010-02-09 16:43:52 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-09 16:59:41 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-11 03:15:01 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-11 06:26:15 from Дмитрий Фефелов <fozzy(at)ac-sw(dot)com>
2010-02-11 12:09:33 from Bart Samwel <bart(at)samwel(dot)tk>
2010-02-11 12:25:28 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-02-11 12:39:49 from Bart Samwel <bart(at)samwel(dot)tk>
2010-02-11 12:41:12 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-11 12:48:14 from Bart Samwel <bart(at)samwel(dot)tk>
2010-02-11 13:04:46 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-11 16:17:40 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-11 16:39:11 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-02-15 19:11:34 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-15 20:03:21 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-15 20:05:29 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-16 14:28:57 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-16 14:31:44 from Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
2010-02-16 20:17:32 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-17 03:12:50 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-15 19:51:26 from Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
2010-02-16 14:22:00 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-17 22:52:14 from Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
2010-02-21 12:37:50 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-26 02:48:05 from Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
2010-02-26 03:19:21 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-26 03:40:35 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 04:01:14 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-26 04:25:46 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-28 14:12:32 from Greg Stark <gsstark(at)mit(dot)edu>
2010-02-26 04:28:14 from Alex Hunsaker <badalex(at)gmail(dot)com>
2010-02-26 04:46:20 from Alex Hunsaker <badalex(at)gmail(dot)com>
2010-02-26 05:11:56 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 06:29:41 from Alex Hunsaker <badalex(at)gmail(dot)com>
2010-02-26 15:07:00 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 15:27:30 from Alex Hunsaker <badalex(at)gmail(dot)com>
2010-02-26 16:51:53 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-26 18:11:44 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 16:50:37 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-26 20:02:40 from Alex Hunsaker <badalex(at)gmail(dot)com>
2010-02-26 08:13:31 from Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
2010-02-26 10:20:17 from Jeroen Vermeulen <jtv(at)xs4all(dot)nl>
2010-02-26 14:49:35 from Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
2010-02-26 16:27:11 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 16:54:10 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-26 17:01:59 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 20:11:16 from Yeb Havinga <yebhavinga(at)gmail(dot)com>
2010-02-26 20:29:11 from Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
2010-02-26 20:46:27 from Yeb Havinga <yebhavinga(at)gmail(dot)com>
2010-02-26 22:40:06 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-27 00:03:06 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-27 00:50:11 from Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
2010-02-28 01:01:43 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-28 04:22:00 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-03-01 02:47:38 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-26 18:53:38 from Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
2010-02-26 18:59:22 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 19:22:37 from Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
2010-02-26 19:57:56 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 20:26:05 from Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
2010-02-26 20:47:55 from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
2010-02-26 19:23:22 from Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
2010-03-01 11:18:01 from Yeb Havinga <yebhavinga(at)gmail(dot)com>
2010-02-28 04:20:38 from Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
2010-02-28 07:52:55 from Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
2010-03-01 02:51:14 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-03-02 23:54:28 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-03-03 05:13:46 from Robert Haas <robertmhaas(at)gmail(dot)com>
2010-02-28 08:18:26 from Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
2010-03-02 23:53:56 from Bruce Momjian <bruce(at)momjian(dot)us>
2010-02-18 14:58:25 from "Pierre C" <lists(at)peufeu(dot)com>
2010-02-18 15:09:42 from Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
2010-02-18 16:47:01 from "Pierre C" <lists(at)peufeu(dot)com>
2010-02-18 16:54:31 from Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
2010-02-18 20:19:21 from "Pierre C" <lists(at)peufeu(dot)com>
2010-02-19 02:31:05 from David Christensen <david(at)endpoint(dot)com>
2010-02-19 13:57:51 from Kenneth Marshall <ktm(at)rice(dot)edu>
2010-02-19 17:56:12 from Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
2010-02-11 13:41:02 from Yeb Havinga <yebhavinga(at)gmail(dot)com>
Lists:
pgsql-hackers
On Thu, Feb 11, 2010 at 7:39 AM, Bart Samwel <bart(at)samwel(dot)tk> wrote:
> On Thu, Feb 11, 2010 at 13:25, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>>
>> 2010/2/11 Bart Samwel <bart(at)samwel(dot)tk>:
>> > 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).
>>
>>
>> It could be a good idea. I don't belive to sophisticate methods. There
>> 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?
It depends a great deal on the query, which is one of the things that
makes implementing this rather challenging.
...Robert
In response to
Responses
pgsql-hackers by date
Next :From: Heikki LinnakangasDate: 2010-02-11 12:44:09Subject : Re: Re: [COMMITTERS] pgsql: Make standby server continuously
retry restoring the next WALPrevious :From : Bart SamwelDate : 2010-02-11 12:39:49Subject : Re: Avoiding bad prepared-statement plans.
