Re: Cached Query Plans

On Mon, Apr 14, 2008 at 5:01 PM, Csaba Nagy <nagy(at)ecircle-ag(dot)com> wrote:
> On Mon, 2008-04-14 at 10:55 -0400, Mark Mielke wrote:
> > The other ideas about automatically deciding between plans based on
> > ranges and such strike me as involving enough complexity and logic, that
> > to do properly, it might as well be completely re-planned from the
> > beginning to get the most benefit.
>
> ... except if you hard-wire the most common alternative plans, you still
> get the benefit of cached plan for a wider range of parameter values.
> Not to mention that if you know you'll cache the plan, you can try
> harder planning it right, getting possibly better plans for complex
> queries... you could argue that complex queries tend not to be repeated,
> but we do have here some which are in fact repeated a lot in batches,
> then discarded. So I guess a cached plan discard/timeout mechanism would
> also be nice.

I think ANALYZE on tables involved should _force_ replanning of cached query.
After all, if ANALYZE was fired, then contents changed substantially and
replanning feels like a good idea.

As for planner getting smarter (and slower ;)) -- complex queries tend not
to be repeated -- so it is worth the trouble to plan them carefully. These
would benefit from smarter planer with or without caching.

The problem is with "simple queries", which can be argued are a majority
of queries. its where the caching comes in. If you cache the queries,
you can let the planner be smarter (and slower). If you don't cache, you
probably don't want trade "frequent simple query"'s speed for "once in
a while complex query".

That stated, for me the most important feature is the possibility to
have a good online query statistics. :)

Regards,
Dawid