Re: Caching of Queries

Neil Conway <neilc@samurai.com> writes:

> I think the conclusion of past discussions about this feature is that
> it's a bad idea. Last I checked, MySQL has to clear the *entire* query
> cache when a single DML statement modifying the table in question is
> issued. Not to mention that the feature is broken for non-deterministic
> queries (like now(), ORDER BY random(), or nextval('some_seq'), and so
> on). That makes the feature close to useless for a lot of situations,
> albeit not every situation.

Well there's no reason to assume that just because other implementations are
weak that postgres would have to slavishly copy them.

I've often wondered whether it would make sense to cache the intermediate
results in queries. Any time there's a Materialize node, the database is
storing all those data somewhere; it could note the plan and parameters that
generated the data and reuse them if it sees the same plan and parameters --
including keeping track of whether the source tables have changed or whether
there were any non-immutable functions of course.

This could be quite helpful as people often do a series of queries on the same
basic data. Things like calculating the total number of records matching the
user's query then fetching only the records that fit on the current page. Or
fetching records for a report then having to calculate subtotals and totals
for that same report. Or even generating multiple reports breaking down the
same data along different axes.

--
greg