X-Original-To: pgsql-bugs@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id CF42A925DD8 for ; Fri, 30 May 2003 19:08:24 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 35215-03 for ; Fri, 30 May 2003 19:08:23 -0400 (EDT) Received: from louise.pinerecords.com (louise.pinerecords.com [213.168.176.16]) by developer.postgresql.org (Postfix) with ESMTP id C329C925D3E for ; Fri, 30 May 2003 19:08:22 -0400 (EDT) Received: from louise.pinerecords.com (kala@localhost [127.0.0.1]) by louise.pinerecords.com with ESMTP id h4UN8MvI024742 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Sat, 31 May 2003 01:08:22 +0200 Received: (from kala@localhost) by louise.pinerecords.com (submit) id h4UN8L3Q024741; Sat, 31 May 2003 01:08:21 +0200 Date: Sat, 31 May 2003 01:08:21 +0200 From: Tomas Szepe To: Todd Nemanich Cc: pgsql-bugs@postgresql.org Subject: Re: db growing out of proportion Message-ID: <20030530230821.GG21944@louise.pinerecords.com> References: <20030529163239.GA11101@louise.pinerecords.com> <3ED769E6.3050300@twopunks.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3ED769E6.3050300@twopunks.org> User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/120 X-Sequence-Number: 6081 > [todd@twopunks.org] > > Additionally, you may want to take a look at your query performance. Are > most of your queries doing sequential scans? In my system, the crucial > columns of the primary tables are int8 and float8 fields. I have those > indexed, and I get a serious performance boost by making sure all > SELECT/UPDATE/DELETE queries that use those columns in the WHERE have an > explicit ::int8 or ::float8 (Explain analyze is your friend). During > peak usage, there is an order of magnitude difference (usually 10 to > 15x) between queries doing sequential scans on the table, and queries > doing index scans. Might be worth investigating if your queries are > taking 5 seconds when your DB is fresh. HTH. Yes, I have taken special care to fine-tune all queries on authentic data. The db setup works as expected in whatever respect with the exception of query times deterioration that apparently corelates to the db's on-disk size growth. Thanks for your suggestions, -- Tomas Szepe