When does Postgres cache query plans?
I'm curious under what circumstances Postgres will cache an execution
plan for a query.
Obviously if you create it with the PREPARE statement, it will be cached..
However, if I just run an ad-hoc query such as:
select * from Foo where X < 5;
A few hundred times, will that be cached? What if I run:
select * from Foo where X < :value;
Can that be cached, or will it always be re-evaluated based on the
value of :value? Thanks!
Mike
Mike Christensen <mike@kitchenpc.com> writes:
I'm curious under what circumstances Postgres will cache an execution
plan for a query.
If you're writing raw SQL, never. The assumption is that the
application knows its usage pattern a lot better than the server does,
and if the application is going to re-execute the same/similar statement
a lot of times, the app ought to make use of a prepared statement for
that.
Some client-side code (such as the JDBC driver) will make use of
prepared statements under the hood, so a lot depends on context.
But sending plain SQL with PQexec() does not result in any cached plan.
regards, tom lane
On Wed, Sep 5, 2012 at 3:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Mike Christensen <mike@kitchenpc.com> writes:
I'm curious under what circumstances Postgres will cache an execution
plan for a query.If you're writing raw SQL, never. The assumption is that the
application knows its usage pattern a lot better than the server does,
and if the application is going to re-execute the same/similar statement
a lot of times, the app ought to make use of a prepared statement for
that.Some client-side code (such as the JDBC driver) will make use of
prepared statements under the hood, so a lot depends on context.
But sending plain SQL with PQexec() does not result in any cached plan.
Excellent, that's pretty much what I figured (and would expect)..
It seems SQL Server and Oracle have some weird caching behavior that's
hard to understand and/or predict.. Postgres also seems to be unique
in the fact it even has a PREPARE statement.. MS SQL and Oracle only
provide that feature through the API..
Mike