CODEWITHSUNDEEP
ruby-on-railspostgresql
seand
seand

Reputation: 5286

Does Postgres do statement caching on the server?

I have a Rails/ActiveRecord server utilizing Postgres via the native gem. I was wondering if/how Postgres reuses query plans. For example let's say my app has stuff like this:

ActiveRecord::Base.execute("select foo from t where id=5")
ActiveRecord::Base.execute("select foo from t where id=7")

Will Posgres know that the 2nd SQL is almost identical to the 1st and to reuse existing query plans?

I'm not looking for an answer of "just use models!" or what not; I'm just wondering what happens in the database.

Upvotes: 1

Views: 902

Answers (4)

bobflux
bobflux

Reputation: 11591

Postgres DOES NOT cache query plans.

Named prepared statements are a different thing : if you notice a query is often issued, you can use Prepare, and then execute it multiple times. ActiveRecord might do that, I don't know.

Upvotes: 0

mys
mys

Reputation: 2473

No, there is caching unless you explicitly tell server to create prepared statement object using PREPARE (or driver specific method). You should use prepared statements because they can give to your application SQL injection immunity out of the box.

You statement is too simple i.e execution time is probably much longer than query plan generation. In order to get an advantage (of prepared statements) you need - execute it multiple times during your session - the query is complex (joins, triggers, functions, rules, ...)

Upvotes: 1

alexius
alexius

Reputation: 2576

I think postgresql doesn't cache such plans. Optimal plans can be different when different constants passed to queries. So it's better to generate new plan than use non-optimal. If you need such cache you may use prepared statements or pl/pgsql functions.

Upvotes: 2

francs
francs

Reputation: 9189

I'm not sure if it is right , from my personal opinion , If use a bind variable in your application, then PostgreSQL Server will reuse the esisting query plan without parsed,rewritten the plan again。

Another way is that use PREPARE statement in your appplication, When the PREPARE statement is executed, the specified statement is parsed, rewritten, and planned. When an EXECUTE command is subsequently issued, the prepared statement need only be executed.Thus, the parsing, rewriting, and planning stages are only performed once, instead of every time the statement is executed

More information about "PREPARED statemnet" please vist http://www.postgresql.org/docs/9.0/static/sql-prepare.html

Upvotes: 1

Related Questions