general question about how best to cache expensive query results

I asked this question over in mailing.database.pgsql-sql a couple of
days ago and got no answer. Perhaps the question was ill-phrased, or
perhaps that was not the best forum.

I'm reposting my question in here in the hope that someone can give me
some general advice or suggestions for further research.

--

I have a general question about how best to cache query results that
are too expensive to calculate each time they're needed.

The question is that when I have a query like:

SELECT
e.id, e.title
FROM
entries e
JOIN feeds f ON e.feed_id = f.id
WHERE
f.url = 'http://example.com'
AND e.id > (select id from entries where pub_date <
current_timestamp - interval '5 days' order by pub_date desc limit 1);

The last restriction in the query is the one that is too expensive to
calculate every time its needed. It's purpose is to restrict the
entries to those that are newer than a certain date, except that some
entries don't specify a date, so I use the fact that ids are in
ascending order in order of creation and find the highest id that's at
least 5 days old.

In actuality, I don't need the relevant e.id to be updated more than
once a day or so. It definitely does not need to be realtime, and is
prohibitively expensive to calculate the actual value.

What are general approaches to this kind of problem?

One obvious solution to me is to create a table for storing just this
value, but something seems wrong to me about potentially lots of
little 1-column, 1-row tables.

If there are multiple of this values that need to periodically
updated, then they could all be in 1 miscellaneous table, but then I
would have the problem of deciding what to make the primary key for
each row, and it would have to be hard-coded in the update and select
queries to distinguish this cached value from other cached values in
the same table, as well as dealing with values having different types.

I hope my question is clear enough. Thanks for any suggestions about
best strategies for dealing with this kind of issue.

.

Relevant Pages

  • Sorting records in a query
    ... I am having a problem sorting records in a query. ... entries in a table and when I build my query based on certain fields, ... in the columns, and I have it set up to sort in ascending order, which ... It looks like it sorts anything with a 1 first then the rest later. ...
    (microsoft.public.access.queries)
  • Re: Sorting records in a query
    ... > I am having a problem sorting records in a query. ... > entries in a table and when I build my query based on certain fields, ... > in the columns, and I have it set up to sort in ascending order, which ... > It looks like it sorts anything with a 1 first then the rest later. ...
    (microsoft.public.access.queries)
  • Re: general question about how best to cache expensive query results
    ... The last restriction in the query is the one that is too expensive to ... entries to those that are newer than a certain date, ... ascending order in order of creation and find the highest id that's at ... -- or, more like your original, in case ids might not be ordered ...
    (comp.databases)
  • Re: general question about how best to cache expensive query results
    ... feeds and sometimes don't include the field ... That was my first attempt to limit the entries ... would be the result of that original query. ... presenting the underlying problem and the business requirements. ...
    (comp.databases)
  • Re: Today and Yesterday
    ... I use this query to show me how many entries are in table Information_coll ... > RogueIT, ... > Steve Schapel, Microsoft Access MVP ...
    (microsoft.public.access.queries)