X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id AFCA74758E6 for ; Wed, 30 Apr 2003 10:54:55 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 8AB9C475458 for ; Wed, 30 Apr 2003 10:54:24 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h3UEsRU6022180; Wed, 30 Apr 2003 10:54:28 -0400 (EDT) To: "Peter Darley" Cc: pgsql-performance@postgresql.org Subject: Re: Query Plan far worse in 7.3.2 than 7.2.1 In-reply-to: References: Comments: In-reply-to "Peter Darley" message dated "Wed, 30 Apr 2003 07:39:24 -0700" Date: Wed, 30 Apr 2003 10:54:27 -0400 Message-ID: <22179.1051714467@sss.pgh.pa.us> From: Tom Lane X-Spam-Status: No, hits=-29.3 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,REFERENCES, REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/261 X-Sequence-Number: 1767 "Peter Darley" writes: > SELECT COUNT(*) FROM Border_Shop_List WHERE NOT EXISTS (SELECT Foreign_Key= > FROM Sample WHERE Foreign_Key=3D'Quantum_' || Border_Shop_List.Assignment_= > ID || '_' || Assignment_Year || '_' || Evaluation_ID) What's the datatype of Foreign_Key? I'm betting that it's varchar(n) or char(n). The result of the || expression is text, and so the comparison can't use a varchar index unless you explicitly cast it to varchar: WHERE Foreign_Key = ('Quantum_' || ... || Evaluation_ID)::varchar I think 7.2 had some kluge in it that would allow a varchar index to be used anyway, but we took out the kluge because it was semantically wrong (it would also allow use of a char(n) index in place of a text comparison, which alters the semantics...) regards, tom lane