> > select * from t where
> > a >= a1 and
> > (a > a1 or b >= b1) and
> > (a > a1 or b > b1 or c > c1)
>
> > In about 95% of cases, the planner correctly selects the index
t(a,b,c)
> > and uses it.
>
> I'm surprised it's that good. Why not do
It is. In fact, it's so good, I mistakenly assumed it would get it
right all the time. That led me directly to my current situation.
> select * from t where a >= a1 and b >= b1 and c >= c1
> order by a,b,c
> limit 1 offset 1;
Note: I left off the limit/order part of the query in my original
example.
My previous experience with offset was that it's not practical for this
type of use. Query time degrades when offset gets large...it's
basically n^2/2 for a scan of a table. If offset was pumped up to O(1)
for any sized offset, the problem would be trivial.
Plus, your where clause does not guarantee results.
Imagine:
a b c
2 3 4
4 2 1
c !> c1
The only other way to rewrite the query is thus (pg has much more
trouble with this form):
select * from t where
a > a1 or
(a >= a1 and b > b1) or
(a >= a1 and b >= b1 and c > c1)
Merlin