X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id CF9B7310D28 for ; Tue, 1 Jul 2003 19:46:54 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 13741-06 for ; Tue, 1 Jul 2003 16:46:43 -0300 (ADT) Received: from www.postgresql.com (www.postgresql.com [64.117.225.209]) by svr1.postgresql.org (Postfix) with ESMTP id 05E31310D0C for ; Tue, 1 Jul 2003 16:46:14 -0300 (ADT) Received: from www.pspl.co.in (www.pspl.co.in [202.54.11.65]) by www.postgresql.com (Postfix) with ESMTP id 21A40CF6D41 for ; Tue, 1 Jul 2003 11:08:43 -0300 (ADT) Received: (from root@localhost) by www.pspl.co.in (8.11.6/8.11.6) id h61E3i904421 for ; Tue, 1 Jul 2003 19:33:44 +0530 Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161]) by www.pspl.co.in (8.11.6/8.11.0) with ESMTP id h61E3iQ04415 for ; Tue, 1 Jul 2003 19:33:44 +0530 From: "Shridhar Daithankar" To: pgsql-performance@postgresql.org Date: Tue, 01 Jul 2003 19:32:49 +0530 MIME-Version: 1.0 Subject: Re: Effective Cache Size Reply-To: shridhar_daithankar@persistent.co.in Message-ID: <3F01E1E1.16493.F1FF6A@localhost> In-reply-to: X-mailer: Pegasus Mail for Windows (v4.02) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Archive-Number: 200307/20 X-Sequence-Number: 2313 On 1 Jul 2003 at 15:50, Howard Oblowitz wrote: > The documentation says that Effective Cache Size "sets the optimizer's > assumption > about the effective size of the disk cache ( that is, the portion of the > kernel's disk > cache that will be used for PostgreSQL data files ). > > What then will be the effect of setting this too high? > > And too low? Let's say postgresql is preparing a plan for a scan and it estimates data set size as 100MB whereas your shared buffers+effective cache is 80M. So postgresql would deduce that it would be better off with sequential scan rather than index scan. Where in fact you have much more memory to make a file system cache and the machine can afford index scan. There is nothing too low or too high of a setting. This isn't exactly performance tuning paramter as other. This is more of information to postgresql. The closer it gets to truer, the plans produced would get optimal. About how to set this parameter, it is roughly effective cache size= (Physical RAM size-shared buffers-requirement for other apps) * 0.8 This is very very rough. You need to make sure that some setting does not trigger a swap avelanche HTH Bye Shridhar -- Slous' Contention: If you do a job too well, you'll get stuck with it.