X-Original-To: pgsql-performance@postgresql.org Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 84144475E4D for ; Fri, 14 Mar 2003 12:12:03 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2899651; Fri, 14 Mar 2003 09:11:55 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Max Baker , Robert Treat Subject: Re: speeding up COUNT and DISTINCT queries Date: Fri, 14 Mar 2003 09:10:06 -0800 User-Agent: KMail/1.4.3 Cc: Greg Stark , Joe Conway , Manfred Koizar , PostgreSQL Performance Mailing List References: <20030312223811.GM30411@warped.org> <1047585930.23128.913.camel@camel> <20030313202254.GD30411@warped.org> In-Reply-To: <20030313202254.GD30411@warped.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200303140910.06416.josh@agliodbs.com> X-Archive-Number: 200303/98 X-Sequence-Number: 1396 Max, > I'm not starting with fresh data every time, I'm usually checking for > an existing record, then setting a timestamp and a boolean flag. > > I've run some profiling and it's about 8000-10,000 UPDATEs every 4 > hours. These are accompanied by about 800-1000 INSERTs. If these are wide records (i.e. large text fields or lots of columns ) you may want to consider raising your max_fsm_relation in postgresql.conf slightly, to about 15,000. You can get a better idea of a good FSM setting by running VACUUM FULL VERBOSE after your next batch (this will lock the database temporarily) and seeing how many data pages are "reclaimed", in total, by the vacuum. Then set your FSM to at least that level. And has anyone mentioned REINDEX on this thread? -- Josh Berkus Aglio Database Solutions San Francisco