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

FWIW this is a hard problem; Oracle is the only database I know of
that's tackled it.

On Wed, Dec 01, 2004 at 11:38:25AM +1100, Neil Conway wrote:
> On Tue, 2004-11-30 at 22:19 +0000, PostgreSQL Bugs List wrote:
> > This means that using a prepared statement instead of a direct query is *40*
> > times slower!
>
> Yes, it's a known (documented) issue that you can get inferior query
> plans using prepared statements. I don't know of an easy way to fix
> this: we cannot infer the value of the prepared query's parameters when
> the planning is done.
>
> An incremental improvement would be to delay preparing queries until the
> first time they are executed (so PREPARE would just store the query in
> an internal hash table, and the first EXECUTE would do the planning,
> then store the plan in the hash table). This would allow us to make use
> of the constant values of query parameters, but there is no guarantee
> that those constant values will be the same for future EXECUTEs (i.e. we
> could actually see worse performance in the aggregate).
>
> A more sophisticated approach would be to do something along the lines
> of generating multiple plans at PREPARE time and then choosing the
> "best" plan for a given EXECUTE (by matching the supplied query
> parameters to the closest guessed set of parameters chosen by PREPARE).
> This is a _hard_ problem though, especially in the presence of multiple
> parameters.
>
> > Note: the same prepared statement works well with other typical
> > databases (e.g. MySQL, SQLite).
>
> This isn't really relevant -- the fact that we fall over for this
> particular query is as much bad luck as anything else.
>
> -Neil
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"