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



Thu, 15 Oct 1998 10:56:50 +0200
Andreas Zeugswetter <andreas.zeugswetter@telecom.at>
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

Thu, 15 Oct 1998 12:01:13 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: order by and index path