X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E671BD1B4EB for ; Thu, 30 Oct 2003 21:33:29 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 41511-07 for ; Thu, 30 Oct 2003 17:33:00 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 0BB93D1B50A for ; Thu, 30 Oct 2003 17:32:58 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 6E13536D16; Thu, 30 Oct 2003 16:32:44 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1AFKPM-0006Cf-00; Thu, 30 Oct 2003 16:32:44 -0500 To: Rajesh Kumar Mallah Cc: Greg Stark , Christopher Browne , pgsql-performance@postgresql.org, Jesse Vincent Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. References: <33298.203.145.130.142.1067457626.squirrel@mail.trade-india.com> <20031029202920.GZ7337@pallas.fsck.com> <33402.203.145.130.142.1067460429.squirrel@mail.trade-india.com> <200310301702.00275.mallah@trade-india.com> <13042.61.16.154.82.1067523120.squirrel@mail.trade-india.com> <3FA323C2.2060302@trade-india.com> <87r80ud0nw.fsf@stark.dyndns.tv> <3FA34827.1030705@trade-india.com> In-Reply-To: <3FA34827.1030705@trade-india.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 30 Oct 2003 16:32:43 -0500 Message-ID: <87ad7icsxw.fsf@stark.dyndns.tv> Lines: 30 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/779 X-Sequence-Number: 4527 Rajesh Kumar Mallah writes: > Nopes the query are not Equiv , earlier one returns 4 rows and the below one > none, Sorry, i lowercased a string constant and dropped the lower() on email. Try this: SELECT * FROM tickets WHERE id IN ( SELECT groups.instance FROM groups JOIN principals ON (groups.id = principals.objectid) JOIN cachedgroupmembers ON (principals.id = cachedgroupmembers.groupid) JOIN users ON (cachedgroupmembers.memberid = users.id) WHERE lower(users.emailaddress) = 'mallah_rajesh@yahoo.com' AND groups.domain = 'RT::Ticket-Role' AND groups.type = 'Requestor' AND principals.principaltype = 'group' ) AND type = 'ticket' AND effectiveid = tickets.id AND (status = 'new' OR status = 'open') ORDER BY priority DESC LIMIT 10; -- greg