Re: Prepared statements performance

On Thu, 10 May 2012 13:52:29 +0200, Alban Hertroys wrote:
> On 10 May 2012 11:30, Daniel McGreal <daniel.mcgreal@redbite.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.
> --
> If you can't see the forest for the trees,
> Cut the trees and you'll see there is no forest.
May I ask what kind of planning may occur during insert?
Regards,
Radek