Re: BUG #1334: PREPARE creates bad execution plan (40x

Tom Lane wrote:
> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> > FWIW this is a hard problem; Oracle is the only database I know of
> > that's tackled it.
>
> It seems fair to point out that this is the tradeoff you must buy into
> when using PREPARE. You can have a query plan that is tailored to the
> specific parameter value you supply, or you can have a generic query
> plan. The tailored plan will cost you planning time; the generic plan
> will save you planning time; but there's no free lunch. If your table
> statistics are such that you really need different plans for different
> parameter values, then you shouldn't be using PREPARE. I do not think
> this is a database bug --- it looks more like DBA misuse of the
> available tools.

There are a few PREPARE items on the TODO list. The big one is that the
SQL PREPARE is not delayed until the first execute so no actual
parameter values are used to generated the prepared plan. libpq prepare
does do this already.

The full TODO item is:

* Allow finer control over the caching of prepared query plans

Currently, queries prepared via the libpq API are planned on first
execute using the supplied parameters --- allow SQL PREPARE to do the
same. Also, allow control over replanning prepared queries either
manually or automatically when statistics for execute parameters
differ dramatically from those used during planning.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073