Re: Prepared statements performance

On Thu, May 10, 2012 at 6:52 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
> On 10 May 2012 11:30, Daniel McGreal <daniel(dot)mcgreal(at)redbite(dot)com> 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