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 17719D1B8AA for ; Thu, 30 Oct 2003 18:46:28 +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 08909-09 for ; Thu, 30 Oct 2003 14:45:57 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id D5DC9D1B58D for ; Thu, 30 Oct 2003 14:45:55 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id D0FC236C80; Thu, 30 Oct 2003 13:45:55 -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 1AFHnv-0005VF-00; Thu, 30 Oct 2003 13:45:55 -0500 To: Rajesh Kumar Mallah Cc: Christopher Browne , pgsql-performance@postgresql.org Cc: 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> In-Reply-To: <3FA323C2.2060302@trade-india.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 30 Oct 2003 13:45:55 -0500 Message-ID: <87r80ud0nw.fsf@stark.dyndns.tv> Lines: 59 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/771 X-Sequence-Number: 4519 Rajesh Kumar Mallah writes: > rt3=# explain > > SELECT DISTINCT main.* > FROM ((( > (Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)) > JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId) > ) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id = CachedGroupMembers_3.GroupId) > ) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id) > ) > WHERE ((main.EffectiveId = main.id)) > AND ((main.Type = 'ticket')) > AND ((( (Users_4.EmailAddress = 'mallah_rajesh@yahoo.com') > AND (Groups_1.Domain = 'RT::Ticket-Role') > AND (Groups_1.Type = 'Requestor') > AND (Principals_2.PrincipalType = 'Group') > )) > AND ((main.Status = 'new') OR (main.Status = 'open')) > ) > ORDER BY main.Priority DESC LIMIT 10; So this query seems to be going the long way around to do the equivalent of an IN clause. Presumably because as far as I know mysql didn't support IN subqueries until recently. Can you do an "explain analyze" on the above query and the following rewritten one in 7.4? The "analyze" is important because it'll give real timing information. And it's important that it be on 7.4 as there were improvements in this area specifically in 7.4. 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 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