Re: Re: [GENERAL] Query caching

The Hermit Hacker <scrappy(at)hub(dot)org> writes:
> Karel, where did things stand the last time this was brought up? We
> haven't gone beta yet, can you re-submit a patch for v7.1 before beta so
> that we can integrate the changes?

I think it would be a very bad idea to try to integrate the query cache
stuff at this point in the 7.1 cycle. The feature needs more
discussion/design/testing than we have time to give it for 7.1.

Some of the concerns I have about it:

1. What is the true performance gain --- if any --- in real-world
situations? The numbers Karel has quoted sound like wildly optimistic
best cases to me. What's the worst case? What's the average case?

2. How do we handle flushing the cache when conditions change (schema
alterations, etc)?

3. Is it really a good idea to use a shared-across-backends cache?
What are the locking and contention costs? What happens when we run
out of shared memory (which is a *very* finite resource)? Will cache
flush work correctly in a situation where backends are concurrently
inserting new plans? Doesn't a shared cache make it nearly impossible
to control the query planner, if the returned plan might have been
generated by a different backend with a different set of
optimization-control variables?

4. How does one control the cache, anyway? Can it be flushed by user
command? How is a new query matched against existing cache entries?
Can one determine which elements of a query are considered parameters to
the cached plan, and which are constants? Does the syntax for doing
these things have anything to do with the SQL standard?

I think this is a potentially interesting feature, but it requires far
more discussion and review than it's gotten so far, and there's no time
to do that unless we want to push out 7.1 release a lot more. I'm also
concerned that we will need to focus heavily on testing WAL during 7.1
beta, and I don't want a major distraction from that...

regards, tom lane