> On Mar 21, 2018, at 2:09 PM, Tim Cross <theophilusx@gmail.com> wrote:
>
>
> a simple question I wasn't able to get a clear answer on....
>
> It is general best practice to use prepared statements and parameters
> rather than concatenated strings to build sql statements as mitigation
> against SQL injection. However, in some databases I've used, there is
> also a performance advantage. For example, the planner may be able to
> more easily recognise a statement and reuse an existing plan rather than
> re-planning the query.
>
> I wasn't sure what the situation is with postgres - is there a
> performance benefit in using prepared statements over a query string
> where the values are just concatenated into the string?
There are two separate things.
Parameterized queries are a query made by your code such that
the values are passed in alongside SQL that has placeholders
such as $1, $2, ... They're what help save you from SQL injection.
A prepared statement is a reference to a query that has previously
been passed to the database, and likely pre-interpreted and planned,
that's ready to accept parameters and run.
Using a prepared statement saves the planner from having to decide
on a plan to run the query, which saves you planning time. But it
does that by preparing a generic plan that'll work for any bound
parameter. The planner might be able to come up with a specific
plan based on the particular values passed in that is better than
the generic plan, so a naive implementation of prepared statements
might lead to the execution of the query being slower in some cases,
as it uses a generic plan when a specific one might be better.
Postgresql avoids the worst cases of that by only switching to a
generic plan for a prepared statement after it's re-planned it
a few times with specific values, and the specific plans have
been costed more expensive than the generic one (or something
like that).
The generic plan is also frozen in to the prepared statement, so
if the data statistics vary significantly during the lifetime of the
prepared statement the plan may no longer be a particularly
good one.
Prepared statements are certainly useful, but choosing whether
to use them or not isn't quite as simple as "it'll avoid the planning
overhead".
Parameterized queries are almost always a good idea.
Cheers,
Steve