Skip site navigation (1) Skip section navigation (2)

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

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: Stefan Keller <sfkeller(at)gmail(dot)com>
Cc: Andy Colson <andy(at)squeakycode(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
Date: 2012-02-28 15:22:27
Message-ID: CAMkU=1wJ7ciYC_qX1es8oGQSc08jM7Vpvg52ahxU39ao93EC0w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Tomas VondraDate: 2012-02-28 16:05:08
Subject: Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: 回复: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
Previous:From: Jeff JanesDate: 2012-02-28 15:14:46
Subject: Re: 回复: [PERFORM] PG as in-memory db? How to w?==?UTF-8?Q?arm up and re-populate buffers? How to read in all tuples in?==?UTF-8?Q?to memory?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group