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 B54A1D1DB16 for ; Mon, 22 Mar 2004 16:55:26 +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 91887-03 for ; Mon, 22 Mar 2004 12:55:33 -0400 (AST) Received: from anchor-post-37.mail.demon.net (anchor-post-36.mail.demon.net [194.217.242.86]) by svr1.postgresql.org (Postfix) with ESMTP id CCF68D1D64C for ; Mon, 22 Mar 2004 12:55:22 -0400 (AST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-37.mail.demon.net with esmtp (Exim 3.35 #1) id 1B5Si5-0001Gz-0b; Mon, 22 Mar 2004 16:55:33 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id EC32417A3E; Mon, 22 Mar 2004 16:55:31 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 3A3D816E5D; Mon, 22 Mar 2004 16:55:29 +0000 (GMT) From: Richard Huxton To: Joseph Shraibman , pgsql-performance@postgresql.org Subject: Re: two seperate queries run faster than queries ORed together Date: Mon, 22 Mar 2004 16:55:28 +0000 User-Agent: KMail/1.5 References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200403221655.28667.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/288 X-Sequence-Number: 6144 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. I'd start by analyze-ing the table in question, and if that doesn't have any effect look at the column stats and see what spread of values it thinks you have. -- Richard Huxton Archonet Ltd