Re: pg_prewarm

On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)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;

> Can this load only the specified index into cache?

Yes. The relation can be anything that has storage, so you can
prewarm either a table or an index (or even a sequence or TOAST table,
if you're so inclined).

> When the relation is too huge to fit into the cache and most access pattern
> in the system is index scan, DBA might want to load only index rather
> than table.
> For such system, so far I've been suggesting using pgstatindex, but it's good
> if pg_prewarm can do that

pgstatindex is an interesting idea; hadn't thought of that. Actually,
though, pgstaindex probably ought to be using a BufferAccessStrategy
to avoid trashing the cache. I've had reports of pgstatindex
torpedoing performance on production systems.

> This utility might be helpful to accelerate a recovery of WAL record not
> containing FPW. IOW, before starting a recovery, list the relations to recover
> from WAL files by using xlogdump tool, load them into cache by using
> this utility,
> and then start a recovery.

Interesting idea.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company