X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 72F63476341 for ; Wed, 7 May 2003 12:27:01 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id B14DE475EF0 for ; Wed, 7 May 2003 12:26:59 -0400 (EDT) Received: (qmail 27303 invoked by uid 500); 7 May 2003 16:28:15 -0000 Date: Wed, 7 May 2003 11:28:15 -0500 From: Bruno Wolff III To: Ryan Cc: pgsql-performance@postgresql.org Subject: Re: Yet another 'why does it not use my index' question. Message-ID: <20030507162815.GA27158@wolff.to> Mail-Followup-To: Ryan , pgsql-performance@postgresql.org References: <11207.65.102.128.233.1052316709.squirrel@fordparts.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <11207.65.102.128.233.1052316709.squirrel@fordparts.com> User-Agent: Mutt/1.5.4i X-Spam-Status: No, hits=-38.0 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MUTT 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/84 X-Sequence-Number: 1863 On Wed, May 07, 2003 at 09:11:49 -0500, Ryan wrote: > I wanted to do the following: > > midas=# explain analyze select * from zip where zip in > (select option_value from client_options where option_name = 'ZIP_CODE' ); Until 7.4 comes out IN will be slow and you should use a join to do this. > midas=# explain analyze select * from zip z, client_options c where > c.option_name = 'ZIP_CODE' and c.option_value = z.zip; I think the problem here might be related to option_value being text and zip being char varying. This might prevent an index from being used to do the join.