Обсуждение: Prepared queries and ANALYZE
Hi all, 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? Thanks, Gavin
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 niceto 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