Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

Hi

2012/2/28 Jeff Janes <jeff.janes@gmail.com> wrote:
> It is hard to figure out what problem you are facing.  Is your data
> not getting loaded into cache, or is it not staying there?

One could say both:
I'd like to warm up the cache befor hand in order to speed up the
first query right away.
And it's not staying there because when there comes a second slightly
different query it's slow again and I would expect that the tuples of
that table stay.

>> Just after the second query. You can try it yourself online here:
>> http://bit.ly/A8duyB

I should have said after the first query.

> The second instance of the exact same query is fast.

Right.

> How long until all similar but not identical queries are fast?

Good question. Can't tell for sure because it not so easy to make it repeatable.
I tested the following:

SELECT count(*) FROM osm_point WHERE tags @> 'amenity=>restaurant'

SELECT count(*) FROM osm_point WHERE tags @> 'cuisine=>pizza'

SELECT count(*) FROM osm_point WHERE tags @> 'tourism=>hotel'

SELECT count(*) FROM osm_point WHERE tags @> 'historic=>castle'

SELECT count(*) FROM osm_point WHERE tags @> 'natural=>peak'
AND to_number(ele, '9999') >= 4000

I would say that after the 4th query it remains fast (meaning less
than a second).

-Stefan

P.S. And yes, the database is aka 'read-only' and truncated and
re-populated from scratch every night. fsync is off so I don't care
about ACID. After the indexes on name, hstore and geometry are
generated I do a VACUUM FULL FREEZE. The current installation is a
virtual machine with 4GB memory and the filesystem is "read/write".
The future machine will be a pizza box with 72GB memory.