two seperate queries run faster than queries ORed together

From: Joseph Shraibman <jks(at)selectacast(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: two seperate queries run faster than queries ORed together
Date: 2004-03-18 21:21:32
Message-ID: c3d3sm$fdg$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kevin Brown 2004-03-19 00:41:12 Re: [HACKERS] fsync method checking
Previous Message Kurt Roeckx 2004-03-18 21:09:51 Re: [HACKERS] fsync method checking