X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B2C71475957 for ; Thu, 13 Feb 2003 15:08:15 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id B3897474E61 for ; Thu, 13 Feb 2003 15:08:14 -0500 (EST) Received: from [216.135.165.74] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 4.0.2) with HTTP id 2838434; Thu, 13 Feb 2003 12:08:22 -0800 From: "Josh Berkus" Subject: Re: cost and actual time To: Chantal Ackermann , pgsql-performance@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.4.0.2 Date: Thu, 13 Feb 2003 12:08:22 -0800 Message-ID: In-Reply-To: <3E4BDE8B.5040908@biomax.de> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/61 X-Sequence-Number: 1117 Chantal,=20 > Sort Key: disease.disease_name, disease_occurrences.sentence_id > -> Nested Loop (cost=3D0.00..6922.38 rows=3D98 width=3D64) (actual > time=3D61.49..275047.46 rows=3D18910 loops=3D1) > -> Nested Loop (cost=3D0.00..6333.23 rows=3D98 width=3D28) (actual=20 > time=3D61.42..274313.87 rows=3D18910 loops=3D1) > -> Nested Loop (cost=3D0.00..5894.04 rows=3D64 width=3D16) (actual= =20 > time=3D32.00..120617.26 rows=3D46849 loops=3D1) >=20 > I tried to tweak the conf settings, but I think I already reached > quite a good value concerning shared buffers and sort mem. the > database is vacuum full analyzed. indexes seem fine. You *sure* that you've vacuum analyzed recently? The planner above is choosing a bad plan because its row estimates are way off ... if the subquery was actually returning 98 rows, the plan above would make sense ... but with 18,000 rows being returned, a Nested Loop is suicidal. Perhaps you could post the full text of the query? If some of your criteria are coming from volatile functions, then that could explain why the planner is so far off ... -Josh Berkus