X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 11186476355 for ; Sat, 3 May 2003 14:36:02 -0400 (EDT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by postgresql.org (Postfix) with ESMTP id 4CF46475A8D for ; Sat, 3 May 2003 14:35:31 -0400 (EDT) Received: from [192.168.5.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.0.4) with ESMTP-TLS id 1727827; Sat, 03 May 2003 12:11:03 -0700 Message-ID: <3EB40BCF.9010203@joeconway.com> Date: Sat, 03 May 2003 11:34:55 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Becky Neville Cc: andrew@libertyrms.info, pgsql-performance@postgresql.org Subject: Re: NOT IN doesn't use index? (fwd) References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-37.5 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,NO_DNS_FOR_FROM, QUOTED_EMAIL_TEXT,REFERENCES,REPLY_WITH_QUOTES, USER_AGENT_MOZILLA_UA 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/28 X-Sequence-Number: 1807 Becky Neville wrote: > Here is the EXPLAIN output from the two queries. The first is the one > that uses WHERE field NOT IN ( 'a','b' etc ). The second is the (much Unless you are working with Postgres 7.4devel (i.e. cvs HEAD), the IN construct is notoriously slow in Postgres. In cvs it is vastly improved. Also, as I mentioned in the other reply, send in "EXPLAIN ANALYZE" results instead of "EXPLAIN" (and make sure you run "VACUUM ANALYZE" first). Joe