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@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026