X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E091B476228 for ; Tue, 4 Mar 2003 12:15:25 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 36450-08 for ; Tue, 4 Mar 2003 12:15:24 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 1B372476224 for ; Tue, 4 Mar 2003 12:15:24 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2879411; Tue, 04 Mar 2003 09:15:13 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "Paul McKay" , "'Tom Lane'" Subject: Re: Slow query performance on large table Date: Tue, 4 Mar 2003 09:14:44 -0800 User-Agent: KMail/1.4.3 Cc: References: <000001c2e268$b1e47210$0c64a8c0@paulspc> In-Reply-To: <000001c2e268$b1e47210$0c64a8c0@paulspc> MIME-Version: 1.0 Message-Id: <200303040914.44390.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/12 X-Sequence-Number: 1310 Paul, > Index Scan using idx_measurement_assessment on measurement > (cost=3D0.00..34668.61 rows=3D261 width=3D8) (actual time=3D26128.07..220= 584.69 > rows=3D503 loops=3D1) > Total runtime: 220587.06 msec These query results say to me that you need to do both a VACUUM FULL and a= =20 REINDEX on this table. The 26-second delay before returning the first row= =20 says "table/index with lots of dead pages" to me. For the future, you should consider dramatically increasing your FSM settin= gs=20 and working a regular VACUUM FULL and REINDEX into your maintainence jobs. --=20 Josh Berkus Aglio Database Solutions San Francisco