X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2A5CB475F00 for ; Tue, 29 Apr 2003 09:04:34 -0400 (EDT) Received: from nat.inquent.com (CPE00508b028d7d-CM00803785c5e0.cpe.net.cable.rogers.com [24.103.22.28]) by postgresql.org (Postfix) with ESMTP id 372CB475F00 for ; Tue, 29 Apr 2003 09:03:58 -0400 (EDT) Received: from [127.0.0.1] (localhost [127.0.0.1]) by nat.inquent.com (8.12.8p1/8.12.8) with ESMTP id h3TD49Mn077441; Tue, 29 Apr 2003 09:04:09 -0400 (EDT) (envelope-from rbt@rbt.ca) Subject: Re: Optimizer not using index when it should From: Rod Taylor To: Rob Messer Cc: Postgresql Performance In-Reply-To: <20030429090301.13152.qmail@web41213.mail.yahoo.com> References: <20030429090301.13152.qmail@web41213.mail.yahoo.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-Ovj/BN9AuBu3Q4vbTld/" Organization: Message-Id: <1051621449.76105.9.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.4 Date: 29 Apr 2003 09:04:09 -0400 X-Spam-Status: No, hits=-36.5 required=5.0 tests=BAYES_10,IN_REP_TO,NO_DNS_FOR_FROM,PGP_SIGNATURE_2, QUOTED_EMAIL_TEXT,REFERENCES,REPLY_WITH_QUOTES, USER_AGENT_XIMIAN autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/240 X-Sequence-Number: 1746 --=-Ovj/BN9AuBu3Q4vbTld/ Content-Type: text/plain Content-Transfer-Encoding: quoted-printable > What can I do to fix this -- is there something I am missing about > setting statistics or some configuration variable I can change? Any > insights would be greatly appreciated. Thank you, If you look at the estimates for cost, the index scan is more expensive by ~1/8th. But as you've shown, it's not. You might try adjusting the random_page_cost down to something more appropriate for your hardware and situation. Do testing... this may cause other queries to use an index scan when they should have been doing a sequential scan. Mistakenly using an index can be a much more costly error (hence the high default random_page_cost). --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-Ovj/BN9AuBu3Q4vbTld/ Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (FreeBSD) iD8DBQA+rnhI6DETLow6vwwRAoncAJ457zHHTudzl3EDklw5xiUhlWiNwwCfWRwZ hzRGx/zN3z6wKyqBDffwNmw= =pEbG -----END PGP SIGNATURE----- --=-Ovj/BN9AuBu3Q4vbTld/--