X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 12D665E4700 for ; Tue, 31 Aug 2004 15:46:24 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 14405-05 for ; Tue, 31 Aug 2004 18:46:18 +0000 (GMT) Received: from frank.wiles.org (frank.wiles.org [24.124.39.75]) by svr1.postgresql.org (Postfix) with ESMTP id 4A0C85E46FB for ; Tue, 31 Aug 2004 15:46:17 -0300 (ADT) Received: from kungfu (frank.wiles.org [24.124.39.75]) by frank.wiles.org (8.12.11/8.12.11) with SMTP id i7VIlQOO010642; Tue, 31 Aug 2004 13:47:27 -0500 Date: Tue, 31 Aug 2004 13:46:19 -0500 From: Frank Wiles To: Ron St-Pierre Cc: pgsql-performance@postgresql.org Subject: Re: Table UPDATE is too slow Message-Id: <20040831134619.4ead03b0.frank@wiles.org> In-Reply-To: <4134BF36.3000504@syscor.com> References: <4134BF36.3000504@syscor.com> X-Mailer: Sylpheed version 0.9.10 (GTK+ 1.2.10; i386-redhat-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/472 X-Sequence-Number: 8108 On Tue, 31 Aug 2004 11:11:02 -0700 Ron St-Pierre wrote: > Postgres 7.4.3 > debian stable > 2 GB RAM > 80 DB IDE drive (we can't change it) > > shared_buffers = 2048 > sort_mem = 1024 > max_fsm_pages = 40000 > checkpoint_segments = 5 > random_page_cost = 3 I agree with all of the follow ups that having indexes on every column is a bad idea. I would remove the indexes from the least searched upon 10-20 columns, as I'm sure this will help your performance. You mention that not having indexes on some of the columns because it "may slow down user queries". I think you should investigate this and make sure they are necessary. I've seen many an application, with far more rows than you're dealing with, only need 1 or 2 indexes, even when all (or most) columns could be searched. Also, you should consider increasing your shared_buffers and probably your sort memory a touch as well. This will help your overall performance. --------------------------------- Frank Wiles http://www.wiles.org ---------------------------------