Re: prepared statements and analyze in 7.3

On Mon, 2002-12-16 at 11:32, Harald Krake wrote:
> does an ANALYZE invalidate the cached query plans of prepared statements?

No.

> I wonder whether I have to re-prepare after an ANALYZE for long running
> prepared statements to benefit from possibly optimized query plans.

Yes, you'll have to.

Note also that the query plan produced by 'EXPLAIN <statement>' is not
necessarily the same one that will be used when the statement is
prepared (due to the absence of literals in the prepared query). As the
PREPARE reference page notes:

In some situations, the query plan produced by PostgreSQL for a prepared
query may be inferior to the plan produced if the query were submitted
and executed normally. This is because when the query is planned (and
the optimizer attempts to determine the optimal query plan), the actual
values of any parameters specified in the query are unavailable.
PostgreSQL collects statistics on the distribution of data in the table,
and can use constant values in a query to make guesses about the likely
result of executing the query. Since this data is unavailable when
planning prepared queries with parameters, the chosen plan may be
sub-optimal.

Cheers,

Neil
--
Neil Conway <neilc(at)samurai(dot)com> || PGP Key ID: DB3C29FC