Neil Conway <nconway@klamath.dyndns.org> writes:
> When processing INSERTs, UPDATEs and DELETEs, check if the query
> would affect any of the tables for which we are maintaing this cache. If
> so, flush the cache. This ensures that we will never return invalid
> results.
Note that this would imply that the cache is *global* across all
backends; therefore it is a shared data structure and hence an access
bottleneck. (Not to mention a memory-management headache, since the
size of shared memory can't easily be varied on-the-fly.)
I cannot believe that caching results for literally-identical queries
is a win, except perhaps for the most specialized (read brain dead)
applications. Has anyone looked at the details of the test case that
MySQL uses to claim that this is a good idea? Has it got any similarity
to your own usage patterns?
We have talked about caching query plans for suitably-parameterized
queries, but even that seems unlikely to be a huge win; at least I'd
not think it useful to try to drive the cache totally automatically.
If an application could say "here's a query I expect to use a lot,
varying these specific parameters" then caching a plan for that would
make sense.
Now, there are notions of "prepared statements" in many access APIs
that fit this description, and in fact the underlying capability exists
in the backend --- we've just not gotten around to building the
interfaces to tie it all together. *That* would be worth working on.
regards, tom lane