Re: Performance problems with prepared statements

>>> On Fri, Oct 12, 2007 at  9:57 AM, in message
<1192201021.6170.47.camel@localhost.localdomain>, Theo Kramer
<theo@flame.co.za> 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