Re: order by and index path

Jan wrote:
>    If there is an ORDER BY clause, using an index  scan  is  the
>    clever  way  if  the  indexqual  dramatically reduces the the
>    amount of data selected and sorted.   I  think  this  is  the
>    normal case

yes

> (who really selects nearly all rows from a 5M row
>    table?).

Data Warehouse apps

>    This  will hurt if someone really selects most of the rows and the index
>    scan jumps over the disc.

I think this is a non issue, since if a qual is not restrictive enough,
the optimizer should choose a seq scan anyway. Doesn' t it do this already ?

>    But here the programmer should use
>    an  unqualified  query  to  perform  a  seqscan  and  do  the
>    qualification in the frontend application.

I would reformulate this to:
Here the backend should do a seq scan and use the qualification to eliminate
not wanted rows.

Resumee:
You have to look at this from the cost point of view. If there is an order by that can be
done with an index, this will make the index a little more preferrable than for the same
query without the order by, but it should not force the index.
You have to give the sort a cost, so that the index access can be compared to the
seq scan and sort path.

Andreas