Re: When does Postgres cache query plans?

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