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 A9938D1B53E for ; Sat, 1 Nov 2003 05:48:21 +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 57325-08 for ; Sat, 1 Nov 2003 01:47:51 -0400 (AST) Received: from rtlocal.trade-india.com (unknown [61.16.154.82]) by svr1.postgresql.org (Postfix) with SMTP id A6EA1D1B509 for ; Sat, 1 Nov 2003 01:47:47 -0400 (AST) Received: (qmail 5547 invoked from network); 1 Nov 2003 06:04:40 -0000 Received: from unknown (HELO trade-india.com) (unknown) by unknown with SMTP; 1 Nov 2003 06:04:40 -0000 From: Rajesh Kumar Mallah Organization: Infocom Network Limited To: Greg Stark Subject: [ PROBLEM SOLVED ] Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. Date: Sat, 1 Nov 2003 11:17:02 +0530 User-Agent: KMail/1.5.1 Cc: Christopher Browne , pgsql-performance@postgresql.org, Jesse Vincent References: <33298.203.145.130.142.1067457626.squirrel@mail.trade-india.com> <3FA34827.1030705@trade-india.com> <87vfq6bbbn.fsf@stark.dyndns.tv> In-Reply-To: <87vfq6bbbn.fsf@stark.dyndns.tv> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200311011117.02662.mallah@trade-india.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/1 X-Sequence-Number: 4561 Well Sorry everyone , The problem was tracked down to a silly datatype mismatch between two join columns in table Groups(instance) and Tickets(id) (int vs varchar ) 7.4b5 is automatically taking care of this mismatch hence it was getting executed there. But , The problem is will this behaviour not allow to go such mistakes unnoticed? Regards Mallah. On Friday 31 Oct 2003 4:08 am, Greg Stark wrote: > Well, you might want to try the EXISTS version. I'm not sure if it'll be > faster or slower though. In theory it should be the same. > > Hum, I didn't realize the principals table was the largest table. But > Postgres knew that so one would expect it to have found a better plan. The > IN/EXISTS handling was recently much improved but perhaps there's still > room :) > > SELECT * > FROM tickets > WHERE EXISTS ( > SELECT 1 > 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 groups.instance = tickets.id > ) > AND type = 'ticket' > AND effectiveid = tickets.id > AND (status = 'new' OR status = 'open') > ORDER BY priority DESC > LIMIT 10;