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.

The biggies also use this access method.

Greetings
Andreas

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

Hi,

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?

I'm starting to look at the executor code. However this kind of
"optimization" might be better to be done in the optimizer, I don't
know.

FYI, often, using an index to sort a tables is SLOWER than loading all the
rows into psort and sorting them, because the index causes random seeks
all over the table.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

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?

I have modified the TODO list:

* Use indexes in ORDER BY for restrictive data sets, min(), max()(Costin Oproiu)

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)