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