Re: AW: [HACKERS] using a btree index in order by clause?

>> I'm wondering if we can use btree index to sort the results in a
>> certain condition. The idea is, if the order-items in the order by
>> clause have a btree index, then why we need to sort them again?
>
>Real life tests done by bruce (and I also did some on Informix) showed
>that sorting is cheaper/faster than doing the index access, if the index does not
>reduce the result set substantially.
>The index will currently already be used if the where restriction suggests it.
>This leads to presorted data.
>It would be nice if the optimizer could eliminate the sort in this case,
>even though the sort routine behaves well with presorted data,
>but here it does not actually do anything.
>
>I think the index access for order by would actually be a gain for certain cases:
>1. Interactive browsing of data (I want the first row very fast)
>2. Large result sets, that won't fit on temporary disk space.

I think these are big win too.

By the way, max(), min() would be optimized in the same way, I guess.

>The biggies also use this access method.
     ~~~~~~~do you mean commercial RDBMSs?
--
Tatsuo Ishii
t-ishii@sra.co.jp