Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 759FDC76A5C for ; Sun, 26 Feb 2012 15:21:13 -0400 (AST) Received: from li339-205.members.linode.com ([96.126.114.205] helo=squeakycode.net) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S1jei-0002rn-Gf for pgsql-performance@postgresql.org; Sun, 26 Feb 2012 19:21:12 +0000 Received: from [192.168.1.116] (50-83-220-125.client.mchsi.com [50.83.220.125]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: andy) by squeakycode.net (Postfix) with ESMTPSA id C107A16890; Sun, 26 Feb 2012 14:20:59 -0500 (EST) Message-ID: <4F4A861A.3000301@squeakycode.net> Date: Sun, 26 Feb 2012 13:20:58 -0600 From: Andy Colson User-Agent: Mozilla/5.0 (X11; Linux i686; rv:10.0) Gecko/20120131 Thunderbird/10.0 MIME-Version: 1.0 To: Stefan Keller CC: pgsql-performance@postgresql.org, Stephen Frost Subject: Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory? References: <4F4A4DD6.6090904@squeakycode.net> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201202/216 X-Sequence-Number: 46388 On 02/26/2012 01:11 PM, Stefan Keller wrote: > 2012/2/26 Andy Colson 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