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 507DAD1B4F4 for ; Thu, 30 Oct 2003 22:39:07 +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 52551-03 for ; Thu, 30 Oct 2003 18:38:38 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id C28F0D1B559 for ; Thu, 30 Oct 2003 18:38:34 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 9EDA336CA5; Thu, 30 Oct 2003 17:38:36 -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 1AFLR6-0006Ro-00; Thu, 30 Oct 2003 17:38:36 -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 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 Date: 30 Oct 2003 17:38:36 -0500 Message-ID: <87vfq6bbbn.fsf@stark.dyndns.tv> Lines: 30 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/782 X-Sequence-Number: 4530 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; -- greg