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

On Sun, Feb 26, 2012 at 12:37 PM, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
> 2012/2/26 Andy Colson <andy(at)squeakycode(dot)net> wrote:
>>>> How about after you load the data, vacuum freeze it, then do something
>>>> like:
>>>>
>>>> SELECT count(*) FROM osm_point WHERE tags @>  'tourism=>junk'
>>>>
>>>> -Andy
>>>
>>>
>>> That good idea is what I proposed elsewhere on one of the PG lists and
>>> got told that this does'nt help.
>>>
> ...
>> I don't buy that.  Did you test it?  Who/where did you hear this?  And...
>> how long does it take after you replace the entire table until things are
>> good and cached?  One or two queries?
>>
>> After a complete reload of the data, do you vacuum freeze it?
>
> Yes.
>
>> After a complete reload of the data, how long until its fast?
>
> Just after the second query. You can try it yourself online here:
> http://bit.ly/A8duyB

The second instance of the exact same query is fast. How long until
all similar but not identical queries are fast?

Cheers,

Jeff