On Thu, 10 May 2012 13:52:29 +0200, Alban Hertroys 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.
--
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
