X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 27A0647621C for ; Mon, 12 May 2003 23:32:16 -0400 (EDT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 622434758F1 for ; Mon, 12 May 2003 23:32:15 -0400 (EDT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3029486; Mon, 12 May 2003 20:32:21 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: csajl , pgsql-performance@postgresql.org Subject: Re: [repost] partial index / funxtional idx or bad sql? Date: Mon, 12 May 2003 20:32:10 -0700 User-Agent: KMail/1.4.3 References: <20030513004710.58428.qmail@web40312.mail.yahoo.com> In-Reply-To: <20030513004710.58428.qmail@web40312.mail.yahoo.com> MIME-Version: 1.0 Message-Id: <200305122032.10928.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-32.4 required=5.0 tests=BAYES_01,IN_REP_TO,QUOTED_EMAIL_TEXT,REFERENCES, REPLY_WITH_QUOTES,USER_AGENT_KMAIL autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/141 X-Sequence-Number: 1920 Csajl, > i'm using 7.3.2. i tried using EXISTS instead of the IN, but the same > query now returns in seven sceonds as opposed to four with the IN. > classifieds_dual_idx is the btree index on (class_type_id, areacode) > and site_cm_areacode_idx is the btree index on (site_id) only. > there is an index in the areacode table that has both (site_id, areacode) > but it's apparently not being used. would it help the query to use that > index instead? No.=20=20 From the look of things, it's not the index scan that's taking time ... it'= s=20 the subplan, which is doing 61,000 loops. Which is normal for IN, but not= =20 for EXISTS. You run VACUUM ANALYZE? --=20 Josh Berkus Aglio Database Solutions San Francisco