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

[Andy Colson <andy@xxxxxxxxxxxxxxx>]

On 02/26/2012 01:11 PM, Stefan Keller wrote:
> 2012/2/26 Andy Colson<andy@xxxxxxxxxxxxxxx>  wrote:
> > On 02/25/2012 06:16 PM, Stefan Keller wrote:
> > > 1. How can I warm up or re-populate shared buffers of Postgres?
> > > 2. Are there any hints on how to tell Postgres to read in all table
> > > contents into memory?
> > >
> > > Yours, Stefan
> >
> > 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 can accept this approach that users should'nt directly interfere
> with the optimizer. But I think it's still worth to discuss a
> configuration option (per table) or so which tells PG that this table
> contents should fit into memory so that it tries to load a table into
> memory and keeps it there. This option probably only makes sense in
> combination with unlogged tables.
>
> Yours, Stefan

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?

After a complete reload of the data, how long until its fast?

-Andy

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance