Thread: 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