On 10 May 2012 11:30, Daniel McGreal <daniel.mcgreal@xxxxxxxxxxx> wrote: > I put the multi-value inserts in as I was > curious as to why prepared statements would be slower given they only plan > the query once (as also does the multi-value insert, I assume). That's a common misconception. The reason that prepared statements are often slower, is exactly _because_ they only plan the query once. Because the query-plan is stored when the query gets prepared, the same plan gets used for every combination of query parameters, so it has to be a fairly generic query plan. OTOH, the multi-value insert knows exactly what combinations of "parameters" will be used in the query and the query planner can optimise the query for those parameters. It wouldn't surprise me if it would re-evaluate plan branch choices based on which row of values is currently being inserted. I think it's safe to say that prepared statements are only efficient when you're dealing with repeated complicated queries, where preparing the query plan takes a significant amount of time. It'll also shave some time off queries that are inefficient regardless of how you execute them (for example, because the query always needs to perform a sequential scan). They'll also be faster on database servers with a slower query planner than the one in Postgres. In most (all?) other cases, executing the query directly is probably faster. Of course there are other benefits to prepared statements, such as a natural immunity to SQL injection. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
