> On Mar 21, 2018, at 2:09 PM, Tim Cross <theophilusx@xxxxxxxxx> 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
