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