Re: Under what circumstances does PreparedStatement use stored

Tom Lane wrote:
> James Robinson <jlrobins(at)socialserve(dot)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