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