Re: [HACKERS] 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 ?

Yes it does.

>
> > 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.

This cost is compared. The optimizer uses the min-max values for the
column stored in pg_statistic to see how much of the table is being
requested, and decides on an index or not.

Doing the restriction on the fontend sounds kind of cheesy to me.

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026