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.