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.
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
