Re: pg_prewarm

Hi Robert,

Just recently I asked on postgres-performance "PG as in-memory db? How
to warm up and re-populate buffers? How to read in all tuples into
memory?"

Somehow open was, what's the best practice of configuration and
relationship between disk/OS cache vs. Portgres cache

The main conclusion was:
* Do a "tar cf /dev/zero $PG_DATA/base either shortly before or
shortly after the database is created"
* Do a seq scan "SELECT * FROM osm_point".

Is your tool a replacement of those above?

-Stefan


2012/3/9 Robert Haas <robertmhaas@gmail.com>:
> On Fri, Mar 9, 2012 at 10:53 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> On Fri, Mar 9, 2012 at 5:21 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>>>> When a relation is loaded into cache, are corresponding indexes also loaded
>>>> at the same time?
>>>
>>> No, although if you wanted to do that you could easily do so, using a
>>> query like this:
>>>
>>> select pg_prewarm(indexrelid, 'main', 'read', NULL, NULL) from
>>> pg_index where indrelid = 'your_table_name'::regclass;
>>
>> Could that be included in an example?  Maybe admins are expected to
>> know how to construct such queries of the cuff, but I always need to
>> look it up each time which is rather tedious.
>
> Not a bad idea.  I thought of including an "Examples" section, but it
> didn't seem quite worth it for the simple case of prewarming a heap.
> Might be worth it to also include this.
>
>> In the patch:
>>
>> s/no special projection/no special protection/
>
> OK, will fix.
>
>> Thanks for putting this together.
>
> I will confess that it was 0% altruistic.  Not having it was ruining
> my day.  :-)
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers