| 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: | Whole Thread | Raw Message | 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.
| 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 |