From: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Csaba Nagy <nagy(at)ecircle-ag(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Greg Stark <gsstark(at)mit(dot)edu>, "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>, Neil Conway <neilc(at)samurai(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: generalizing the planner knobs |
Date: | 2005-12-05 08:28:39 |
Message-ID: | 1133771319.2906.858.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Sun, 2005-12-04 at 12:49 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
>
> > ISTM we could do some of that with another GUC, lets call it
> > prepare_once = on. The system default is to have a prepared statement
> > bound to a plan on its first parameter bind. If we set this to "off",
> > then the statement will replan each time we bind. This would give us
> > both flexibility and predictability. (As ever, someone suggest a better
> > name?).
>
> Why would all statements behave the same?
They would be flexible and predictable, but not the same.
prepare_once = off
would reoptimize each statement, so each could have a potentially
different plan. Which, in the case I cited, is the only optimal
behaviour: sticking to any one plan, by any method, would be wrong.
The plans would be predictable because performance never exceeds the
worst case SeqScan; planning would be flexible because it will always
take the best plan.
> I think an important
> percentage of cases would require a fixed plan (thus planning at first
> sight is a good idea), while a limited number of cases would require
> planning every time the sentence is called.
Yes, that is exactly what I see. Hence a GUC with a default the same as
it is now: they would only be prepared once. You would only set the GUC
to another value when you have a statement that looks like it needs
"hinting" i.e. the plan flips from SeqScan to IndexScan and back
depending upon the input data.
> Your idea of qualifying it
> by table name does not make too much sense to me, because you can have
> both types of queries for each table, and further any query where this
> is necessary will involve more than one table anyway, so which one do
> you choose to make the decision?
That was a different idea later down my note, not a variation of the
same one: that had nothing to do with the prepare_once concept. Those
options were meant to be set on a per statement basis, not at the server
level.
I was trying to solve Neil's stated problem: How to force one part of a
query to avoid a SeqScan, yet without touching the others.
> So we would provide a protocol/libpq option to allow first-params-
> planning (the default and current behavior), and another to allow
> planning-every-time. The latter would tell the server to save only the
> parsetree of the query and replan each time it is invoked.
Or some function similar. I prefer the GUC because it does not imply a
protocol change.
Best Regards, Simon Riggs
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2005-12-05 08:37:24 | Re: generalizing the planner knobs |
Previous Message | Andrew Dunstan | 2005-12-05 07:02:30 | Re: [PATCHES] snprintf() argument reordering not working |