1

I wonder how much I should care about prepared statements (and therefore try to optimize my code) in case of pretty simple statements.

The question originates from the fact that I recently noticed ActiveRecord (a Ruby ORM) writes different SQL queries depending on the way I chain the commands. Here's an example.

2.1.1 :004 > Account.first.domains.explain
  Account Load (0.5ms)  SELECT "accounts".* FROM "accounts" ORDER BY "accounts"."id" ASC LIMIT 1
  Domain Load (0.8ms)  SELECT "domains".* FROM "domains" WHERE "domains"."account_id" = $1  [["account_id", 1]]
 => EXPLAIN for: SELECT "domains".* FROM "domains"  WHERE "domains"."account_id" = $1 [["account_id", 1]]
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on domains  (cost=0.00..1.35 rows=1 width=222)
   Filter: (account_id = 1)
(2 rows)

vs

2.1.1 :005 > Domain.for_account(Account.first).explain
  Account Load (0.7ms)  SELECT "accounts".* FROM "accounts" ORDER BY "accounts"."id" ASC LIMIT 1
  Domain Load (0.9ms)  SELECT "domains".* FROM "domains" WHERE "domains"."account_id" = 1
 => EXPLAIN for: SELECT "domains".* FROM "domains"  WHERE "domains"."account_id" = 1
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on domains  (cost=0.00..1.35 rows=1 width=222)
   Filter: (account_id = 1)
(2 rows)

As you can see, the first statement generates a prepared statement, whereas the second one does not.

Is there a truly benefit in using prepared statements for such simple queries, or generally speaking the benefits I get in these cases are small enough to not justify major refactoring or workaround at code level?

  • It's dubious that the first statement is prepared, because the plan should show: Filter: (account_id = $1). The point of a prepared statement is that the parameters values are not known at prepare/planning time. – Daniel Vérité Sep 26 '14 at 20:43
  • @DanielV, thats not entirely the case. As of PostgreSQL 9.2 PostgreSQL may choose to produce a per-execution plan if it thinks it will be significantly faster to do so; check out GetCachedPlan in plancache.c. – Craig Ringer Sep 27 '14 at 0:26
0

The difference is negligible much of the time. You'll see significant benefits if:

  • There are lots of joins or lots of predicates; or
  • The query contains long IN lists or similar large data elements

In general, unless it's a "hot" code path where you really hammer a particular query I wouldn't make much effort optimising to focus on re-usable prepared statements.

(You should still parameterise your SQL rather than substituting parameters directly into the text, and people often confuse that with prepraed statements, but you seem to understand the difference.)

  • The parameterized query also having the benefit of additional security against such things as SQL Injection as well. – LowlyDBA Sep 26 '14 at 16:46
  • @JohnMcCall Hence my addendum that parameterising remains important; that doesn't mean you need re-usable prepared statements. Most people confuse the two, but it's possible to parameterise a query that's not a prepared statement, and you can (easily) fail to parameterise a prepared statement too. – Craig Ringer Sep 26 '14 at 16:49
  • Agreed - just wanted to make sure he understood some of the benefits of doing so. – LowlyDBA Sep 26 '14 at 16:51
  • When using JDBC a parameterized statement is always a prepared statement. – a_horse_with_no_name Sep 26 '14 at 17:55
  • @a_horse_with_no_name ... kind of. You always use the PreparedStatement interface. In PgJDBC it may or may not actually be prepared as a named, re-usable statement depending on the prepareThreshold and the number of times the statement has been executed. It always uses Parse/Bind/Describe/Execute but it initially doesn't ask the server to save the plan for re-use. – Craig Ringer Sep 27 '14 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.