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 0F19DD1B4EB for ; Thu, 30 Oct 2003 18:18:53 +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 05781-02 for ; Thu, 30 Oct 2003 14:18:22 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id F3D47D1B4E9 for ; Thu, 30 Oct 2003 14:18:20 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id BC11436AEB; Thu, 30 Oct 2003 13:18:05 -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 1AFHMz-0005S7-00; Thu, 30 Oct 2003 13:18:05 -0500 To: Rajesh Kumar Mallah Cc: Christopher Browne , pgsql-performance@postgresql.org 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:18:05 -0500 Message-ID: <87znfid1ya.fsf@stark.dyndns.tv> Lines: 20 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/768 X-Sequence-Number: 4516 Rajesh Kumar Mallah writes: > -> Seq Scan on tickets main (cost=0.00..465.62 rows=1 width=164) > Filter: ((effectiveid = id) AND (("type")::text = 'ticket'::text) AND (((status)::text = 'new'::text) OR ((status)::text = 'open'::text))) This query has to read through every ticket in the system and check if the current user has access to it? It seems like this isn't going to be a terribly fast query no matter how you slice it. One thing that might help keep its run-time from growing is a partial index WHERE type = 'ticket' and (status = 'new' OR status = 'open') (I'm not sure what the point of the effectiveid=id clause is) That at least might help when 99% of your tickets are old closed tickets. But it will still have to scan through every new and open ticket which on some systems could be a large number. -- greg