>>> On Fri, Oct 12, 2007 at 9:57 AM, in message
<1192201021.6170.47.camel@xxxxxxxxxxxxxxxxxxxxx>, Theo Kramer
<theo@xxxxxxxxxxx> wrote:
>
> select * from foo where
> (a = a1 and b = b1 and c >= c1) or
> (a = a1 and b < b1) or
> (a > a1)
> order by a, b desc, c;
>
> I have, however, found that transforming the above into a union based
> query performs substantially better.
Another approach which often performs better is to rearrange the logic
so that the high-order predicate is AND instead of OR:
select * from foo where
( a >= a1
and ( a > a1
or ( b <= b1
and ( b < b1
or ( c >= c1 )))))
order by a, b desc, c;
With the right index and a limit on rows, this can do particularly well.
-Kevin
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
