At my day job I've been doing a fair amount of routine query and schema optimization and I've noticed on particular query shape that has repeatedly caused problems, and it's one we've talked about before.
select * from table where simple-restriction 0 OR (complex-subquery) For example something like: SELECT * FROM projects WHERE ispublic OR project_id IN (SELECT project_id FROM project_members WHERE userid = ?) Either half of this clause can easily be executed using indexes but the combination forces Postgres to do a full sequential table scan. The solution has been to rewrite each of these cases into: SELECT * FROM projects WHERE ispublic UNION ALL SELECT * FROM projects WHERE NOT ispublic AND project_id IN (SELECT project_id FROM project_members WHERE userid = ?) But there are several problems with this. 1) It's often difficult to get the conditions to be exactly disjoint such that you can use UNION ALL, and if you can't then UNION can be slow and possibly even incorrect. 2) The resulting query is difficult to combine with other clauses. They must either be copied into both sides of the UNION or wrapped around the outside and hope that Postgres pushes them down into the union branches where necessary. More complex conditions can't be pushed down, and in particular combining two conditions that have been rewritten to use union is very difficult. 3) It's extremely difficult to generate this kind of query in an ORM such as ActiveRecord without breaking the abstractions and causing surprising interactions. I'm not sure I have much to offer here. I definitely don't know where to start implementing it. But I know it's come up before on the list and just thought I would mention that I've noticed it being a recurring problem for at least this user. -- greg
