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 41E14D1CB21 for ; Mon, 22 Mar 2004 17:55:32 +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 12618-09 for ; Mon, 22 Mar 2004 13:55:39 -0400 (AST) Received: from tupari.net (node-40242ac2.lga.onnet.us.uu.net [64.36.42.194]) by svr1.postgresql.org (Postfix) with ESMTP id 82D06D1C4EB for ; Mon, 22 Mar 2004 13:55:28 -0400 (AST) Received: from selectacast.net ([192.168.0.2]) by tupari.net (8.12.8/8.12.8) with ESMTP id i2MHtVCE025419; Mon, 22 Mar 2004 12:55:34 -0500 Message-ID: <405F2893.5090805@selectacast.net> Date: Mon, 22 Mar 2004 12:55:31 -0500 From: Joseph Shraibman User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard Huxton Cc: pgsql-performance@postgresql.org Subject: Re: two seperate queries run faster than queries ORed together References: <200403221655.28667.dev@archonet.com> In-Reply-To: <200403221655.28667.dev@archonet.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/292 X-Sequence-Number: 6148 Richard Huxton wrote: > On Thursday 18 March 2004 21:21, Joseph Shraibman wrote: > >>explain >>SELECT COUNT(u.ukey) FROM u, d WHERE d.ukey = u.ukey AND u.pkey = 260 >>AND (u.status = 3 OR d.status = 3 ) AND NOT u.boolfield ; >> >> >> QUERY PLAN >>--------------------------------------------------------------------------- >>------------ Aggregate (cost=128867.45..128867.45 rows=1 width=4) >> -> Hash Join (cost=32301.47..128866.77 rows=272 width=4) >> Hash Cond: ("outer".ukey = "inner".ukey) >> Join Filter: (("inner".status = 3) OR ("outer".status = 3)) >> -> Seq Scan on u (cost=0.00..41215.97 rows=407824 width=6) >> Filter: ((pkey = 260) AND (NOT boolfield)) > > > There's your problem. For some reason it thinks it's getting 407,824 rows back > from that filtered seq-scan. I take it that pkey is a primary-key and is > defined as being UNIQUE? If you actually did have several hundred thousand > matches then a seq-scan might be sensible. > No, pkey is not the primary key in this case. The number of entries in u that have pkey 260 and not boolfield is 344706. The number of those that have status == 3 is 7. To total number of entries in d that have status == 3 is 4. > I'd start by analyze-ing the table in question, Is done every night. The problem is that it seems the planner doesn't think to do the different parts of the OR seperately and then combine the answers.