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 EF549D1E15F for ; Thu, 18 Mar 2004 21:24:06 +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 24022-07 for ; Thu, 18 Mar 2004 17:24:07 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 4CC9DD1C4EB for ; Thu, 18 Mar 2004 17:24:01 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i2ILNw1Y016031 for ; Thu, 18 Mar 2004 21:23:58 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i2ILLQua015797 for pgsql-performance@postgresql.org; Thu, 18 Mar 2004 21:21:26 GMT From: Joseph Shraibman X-Newsgroups: comp.databases.postgresql.performance Subject: two seperate queries run faster than queries ORed together Date: Thu, 18 Mar 2004 16:21:32 -0500 Organization: Hub.Org Networking Services Lines: 50 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/285 X-Sequence-Number: 6141 explain SELECT COUNT(u.ukey) FROM u, d WHERE d.ukey = u.ukey AND u.pkey = 260 AND (u.status = 3 ) AND NOT u.boolfield ; QUERY PLAN ---------------------------------------------------------------------------------------------- Aggregate (cost=45707.84..45707.84 rows=1 width=4) -> Nested Loop (cost=0.00..45707.16 rows=273 width=4) -> Seq Scan on usertable u (cost=0.00..44774.97 rows=272 width=4) Filter: ((pkey = 260) AND (status = 3) AND (NOT boolfield)) -> Index Scan using d_pkey on d (cost=0.00..3.41 rows=1 width=4) Index Cond: (d.ukey = "outer".ukey) explain SELECT COUNT(u.ukey) FROM u, d WHERE d.ukey = u.ukey AND u.pkey = 260 AND (d.status = 3 ) AND NOT u.boolfield ; QUERY PLAN ---------------------------------------------------------------------------------------------- Aggregate (cost=28271.38..28271.38 rows=1 width=4) -> Nested Loop (cost=0.00..28271.38 rows=1 width=4) -> Seq Scan on d (cost=0.00..28265.47 rows=1 width=4) Filter: (status = 3) -> Index Scan using u_pkey on u (cost=0.00..5.89 rows=1 width=4) Index Cond: (("outer".ukey = u.ukey) AND (u.pkey = 260)) Filter: (NOT boolfield) 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)) -> Hash (cost=25682.98..25682.98 rows=1032998 width=6) -> Seq Scan on d (cost=0.00..25682.98 rows=1032998 width=6) ... so what do I do? It would be a real pain to rewrite this query to run twice and add the results up, especially since I don't always know beforehand when it will be faster based on different values to the query.