X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 59B28476376 for ; Sat, 3 May 2003 17:17:20 -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 0CA60475D91 for ; Sat, 3 May 2003 17:16:49 -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 h43LGf6h003193; Sat, 3 May 2003 17:16:41 -0400 (EDT) (envelope-from rbt@rbt.ca) Subject: Re: NOT IN doesn't use index? (fwd) From: Rod Taylor To: Becky Neville Cc: Joe Conway , Postgresql Performance In-Reply-To: References: Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-vgz3izuTIe49H4MG2sJa" Organization: Message-Id: <1051996600.1656.36.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.4 Date: 03 May 2003 17:16:41 -0400 X-Spam-Status: No, hits=-43.8 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,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: 200305/35 X-Sequence-Number: 1814 --=-vgz3izuTIe49H4MG2sJa Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Sat, 2003-05-03 at 15:56, Becky Neville wrote: > I think that list is actually (gulp) hard coded. It's not my query. I a= m=20 > trying to speed it up for someone else - to hopefully learn something in= =20 > the process that isn't dependent on what version of postgres i'm=20 > running :) An interesting test might be to see if the overhead of doing a character based comparison (as opposed to integer based) is significant. If it is, previous tests show it can be significant for CPU bound queries, convert all of those codes into integers and use a lookup table table to do the conversion. Another interesting thought, since you have a long running query would be to attempt an inversion. Create a temporary table with the *valid* codes if count(valid codes) < 2 * count(invalid codes). Run the query replacing NOT IN with a join to the temporary table. This will reduce the number of comparisons required, as a match can move onto the next datum, but a NOT IN must check all values. If this helps, try indexing (and analyzing) the temporary table. By far the fastest results can be achieved by not allowing invalid billing codes to be inserted into the table via a constraint of somekind (check or fkey to summary table). --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-vgz3izuTIe49H4MG2sJa 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+tDG46DETLow6vwwRAoqTAJ9afxXgCKItJ5BtTRZTp8/bceNVbgCfdd30 f2WZpjjmo7yO2VIOVAf/XGg= =yU7A -----END PGP SIGNATURE----- --=-vgz3izuTIe49H4MG2sJa--