X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 3C355CB4E8 for ; Sat, 12 Jul 2003 03:28:54 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 43861-09 for ; Sat, 12 Jul 2003 03:29:12 +0000 (GMT) Received: from mailav.tor1.inquent.com (mail.inquent.com [216.208.117.106]) by svr1.postgresql.org (Postfix) with ESMTP id B79C9CCA32 for ; Fri, 11 Jul 2003 13:20:03 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mailav.tor1.inquent.com (Postfix) with ESMTP id 3786C10257; Fri, 11 Jul 2003 12:13:35 -0400 (EDT) Received: from [10.0.2.7] (unknown [10.0.2.7]) by mailav.tor1.inquent.com (Postfix) with ESMTP id 5365010248; Fri, 11 Jul 2003 12:13:32 -0400 (EDT) Subject: Re: force the use of a particular index From: Rod Taylor To: Scott Cain Cc: Postgresql Performance In-Reply-To: <1057937779.5767.17.camel@localhost.localdomain> References: <1057850280.1451.22.camel@localhost.localdomain> <1057920672.46100.149.camel@jester> <1057929459.5766.7.camel@localhost.localdomain> <1057930695.46100.172.camel@jester> <1057937779.5767.17.camel@localhost.localdomain> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-J+ySi224Ty4kTbhgY9Wh" Message-Id: <1057940450.65544.14.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 11 Jul 2003 12:20:51 -0400 X-Virus-Scanned: by AMaViS perl-11 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/185 X-Sequence-Number: 2478 --=-J+ySi224Ty4kTbhgY9Wh Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Fri, 2003-07-11 at 11:36, Scott Cain wrote: > Rod, >=20 > I see what you mean about the scan on the feature_pkey taking a long > time. I tried several things to remedy that. I created an index on > feature (feature_id,type_id) (which I don't think makes sense since > feature_id is the primary key, so add another column really doesn't It may be the primary key, but the system looked like it was throwing away many rows based on type_id. If it was throwing away many more rows than found, the index with type_id may have been cheaper. It is difficult to tell from an EXPLAIN ANALYZE as it doesn't tell you exactly how many rows were filtered, just the cost to read them and how many were used after the filter. > help). I also created a index on feature (type_id, feature_id), but the > planner doesn't use it. Also, there was an already existing index on > feature (type_id) that the planner never used. It cannot use more than one index for a given table scan at the moment.=20 There are proposals on how to 'fix' that, but those require significant=20 overhauls of various systems. > Any other ideas? Out of curiosity, what do you get if you disable hash joins? set enable_hashjoin =3D false; How about a partial index on (feature_id) where type_id =3D 219? --=-J+ySi224Ty4kTbhgY9Wh Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA/DuPi6DETLow6vwwRAuOwAJ0bdMZTtdts3vQRFRhAzBkNj+AaWgCdE6Bp wQqVL8GRRtWEYOdRbeDfdfI= =l6S/ -----END PGP SIGNATURE----- --=-J+ySi224Ty4kTbhgY9Wh--