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 [EMAIL PROTECTED] 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?" ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
