Re: Again, sorry, caching.

> > Well, the simple query cache scheme that is currently being proposed
> > would use a byte-by-byte comparison of the incoming query. I think the
> > consensus is that for a lot of workloads, this would be a bad idea.
>
> And this is what I have been trying to argue. Many SQL
> deployments execute a
> set of hard coded queries as the majority of the work load. The dynamic
> queries, obviously, will not be cached, but the vast majority of
> work will come
> out of the cache.

I feel like jumping into the fray!

I think first we need to decide on some facts:

1. Implementing a byte-compatible query cache WILL improve the speed of
repetitive queries over static data.

2. This can be incredibly useful for some web applications.

3. It is really hard to implement such a cache whilst keeping postgres
maintainable and ACID compliant.

4. An application layer cache can be smarter and faster than a database
layer cache, and this is currently the standard way of doing things. MySQL
is bringing db layer caches to the mainstream. In a few years time -
everyone might be doing it...

5. The main developers, or in fact the people with the ability to implement
such a thing, either won't do it or can't be stuffed doing it...

6. Implementing prepared statements in postgres is a reasonable, valid and
standard addition that will improve performance all over the place. This
might also lead to "prepared views" - another performance improvement.

7. Improving the buffer manager's LRU policy can reduce problem of seq. scan
wiping out cache.

So, given the above it seems to me that:

1. The main developers are more interested in implementing prepared
statements - which is cool, as this is a good performance improvement.

2. The main developers can look at replacing LRU to futher improve cache
use.

3. We agree that such a query cache can be useful in some circumstances and
could help postgres's performance in certain environments, but the will
doesn't exist to implement it at the moment and it would also be difficult
and messy. Put it on the TODO list maybe.

4. If someone happens to submit a magic patch that implements query caching
in a perfectly ACID-compliant way, then it should be considered for
inclusion. Why the heck not?

Chris