On Thu, May 10, 2012 at 6:52 AM, Alban Hertroys <haramrae@xxxxxxxxx> wrote: > 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. That can be often true, but for simple inserts there is no plan to get wrong. Prepared statements can knock about 30-50% of statement latency off in such cases if you're not i/o bound. Definitely though prepared statements are headache though and I rarely use them. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
