How much should I care about prepared statements for simple queries?

Question

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?

Answer 1

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