Jack Coates <[EMAIL PROTECTED]> writes: > jackdb=# explain SELECT DISTINCT members_.memberid_ > jackdb-# FROM members_ > jackdb-# WHERE ( members_.List_='list1' > jackdb(# AND members_.MemberType_='normal' > jackdb(# AND members_.SubType_='mail' > jackdb(# AND members_.emailaddr_ IS NOT NULL ) > jackdb-# GROUP BY memberid_ HAVING (
Um, that's not what I had in mind at all. Does GROUP BY actually do anything at all here? (You didn't answer me as to whether memberid_ is a unique identifier or not, but if it is, this GROUP BY is just an expensive no-op.) What I was envisioning was pulling the sub-selects up to the top level and using grouping to calculate the count(*) values for all memberids in parallel. Roughly speaking it would look like (again assuming memberid_ is unique) SELECT memberid_ FROM ( SELECT memberid_ FROM lyrActiveRecips, members_, outmail WHERE (all the conditions for this case) UNION ALL SELECT memberid_ FROM lyrCompletedRecips, members_, outmail WHERE (all the conditions for this case) ) GROUP BY memberid_ HAVING count(*) = 3; However, if you can't change the boilerplate part of your query then this is all blue-sky speculation anyway. What I'm actually more interested in is your statement that MSSQL can do the original query quickly. I find that a bit hard to believe because I don't see any relevant optimization techniques. Do they have any equivalent to EXPLAIN that would give some hint how they're doing it? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match