X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E8043475956 for ; Sat, 3 May 2003 15:31:53 -0400 (EDT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by postgresql.org (Postfix) with ESMTP id 446504762A2 for ; Sat, 3 May 2003 15:31:48 -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 1727853; Sat, 03 May 2003 13:07:21 -0700 Message-ID: <3EB41900.5010407@joeconway.com> Date: Sat, 03 May 2003 12:31:12 -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/30 X-Sequence-Number: 1809 Becky Neville wrote: > Well I think you answered my question already, but just in case > here are the explain results again and the query follows (I warned, it is > long.) And I did run VACUUM ANALYZE beforehand. [snipped ugly query with three NOT IN clauses] Hmmm, no surprise that's slow. How are those three lists of constants generated? One idea is to recast this as a left join with a FROM clause subselect, e.g. select uabopen_srat_code from uabopen u left join (select '1F' as uabopen_srat_code union all '1FD' union all '3A' ...) as ss on u.uabopen_srat_code = ss.uabopen_srat_code where ss.uabopen_srat_code is null; But I'm not sure that will be much quicker. If the list of uabopen_srat_code you're filtering on comes from one of the other tables, you might be able to do better -- back to the question above, how is that list generated? What do the other table look like? Joe