Re: Under what circumstances does PreparedStatement use stored

Tom Lane wrote:
> James Robinson <jlrobins@socialserve.com> writes:
>
>>... I suppose this all assumes that the lookup + maintenance of such a
>>datastructure would ultimately cost less than re-planning all queries
>>all the time.
>
>
> I think that is a safe bet to be true, as long as you get *some* mileage
> out of the plan cache.  If the application issues a bunch of
> no-two-alike queries then it's a loss of course.  But doesn't the JDBC
> API distinguish prepared statements from unprepared ones?  ISTM it is
> the app programmer's responsibility to prepare just those statements
> he's likely to use more than once.  I don't think the driver need
> second-guess this choice.

The problem is that JDBC's PreparedStatement provides two things:
repeated execution of the same query with different parameters, and
portable parameterization of queries. So it's likely that many one-shot
or infrequently executed queries will still use a PreparedStatement.

This is why a threshold on PreparedStatement reuse before using
PREPARE/EXECUTE seemed like a good idea -- we should be able to avoid
PREPARE-ing the one-shot queries, at a minimum.

-O