Prepared queries and ANALYZE

Gavin Sherry <swm@linuxworld.com.au> writes:

Looking through the query preparing and stats analyze code, I noticed that
prepared queries using an analyzed table are not replanned. I imagine that
some users of prepared queries would not want the above behaviour, plan
stability etc, but surely others would.
I didn't notice any discusion of this in the list archives. Any technical
reasons why this shouldn't happen?

Well, there are implementation reasons why it doesn't happen: there's no
infrastructure for determining which tables a prepared query depends on
nor for re-planning it if we did notice they'd changed.

But really this is a special case of the problem of "schema changed
underneath a plan", and yes it'd be nice to notice that and invalidate
the plan.

regards, tom lane

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

Tom> But really this is a special case of the problem of "schema
Tom> changed underneath a plan", and yes it'd be nice to notice
Tom> that and invalidate the plan.

In general, pgsql doesn't cache access plans, correct ?

If there was a cache of access plans, repeated queries (not just
prepared by the same client) could use the same plan avoiding
recompilation. The cache will also be a single place in shared memory
where invalidation can take place.

Just my 2c.

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh

On Mon, 28 Apr 2003, Tom Lane wrote:

Gavin Sherry <swm@linuxworld.com.au> writes:

Looking through the query preparing and stats analyze code, I noticed that
prepared queries using an analyzed table are not replanned. I imagine that
some users of prepared queries would not want the above behaviour, plan
stability etc, but surely others would.
I didn't notice any discusion of this in the list archives. Any technical
reasons why this shouldn't happen?

Well, there are implementation reasons why it doesn't happen: there's no
infrastructure for determining which tables a prepared query depends on
nor for re-planning it if we did notice they'd changed.

Yes. Speaking to Bruce on IRC I was reminded that prepared queries exist
on a single backend which makes such this problem less pertinent than a
general solution, which you mentioned.

Gavin