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?

2
  • 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. Sep 26, 2014 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. Sep 27, 2014 at 0:26

1 Answer 1

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.)

5
  • The parameterized query also having the benefit of additional security against such things as SQL Injection as well. Sep 26, 2014 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. Sep 26, 2014 at 16:49
  • Agreed - just wanted to make sure he understood some of the benefits of doing so. Sep 26, 2014 at 16:51
  • When using JDBC a parameterized statement is always a prepared statement.
    – user1822
    Sep 26, 2014 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. Sep 27, 2014 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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