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

On Tue, Feb 28, 2012 at 12:48 PM, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
> Hi
>
> 2012/2/28 Jeff Janes <jeff(dot)janes(at)gmail(dot)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.

Only the pages needed for a given query are loaded in the first place.
So even if they do stay, a new query that needs different pages
(because it accesses a different part of the index, and of the table)
won't find them already loaded, except by accident.

>
>>> 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).

Hmm. I ran out of example queries before they started being fast.

Cheers,

Jeff