Hi 2012/2/28 Jeff Janes <jeff.janes@xxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
