RE: [HACKERS] What about LIMIT in SELECT ? - Mailing list pgsql-hackers

>
> I know the good part of the posting is coming.
>
> >     Last with the btree index on key  and  the  patched  backend.
> >     This  time the plan is a plain IndexScan because the ORDER BY
> >     clause exactly matches the sort order of the  chosen  index.
> >     S1  needs  13  seconds  and  S2 less than 0.2!  This dramatic
> >     speedup comes from the fact, that this time the index scan is
> >     the  toplevel  executor  node and the executor run is stopped
> >     after 20 tuples have been selected.
>
> OK, seems like in the S1 case, the use of the psort/ORDER BY code on top
> of the index was taking and extra 3 seconds, which is 23%.  That is a
> lot more than I thought for the psort code, and shows we could gain a
> lot by removing unneeded sorts from queries that are already using
> matching indexes.
>
> Just for clarity, added to TODO.  I think everyone is clear on this one,
> and its magnitude is a surprise to me:
>
>   * Prevent psort() usage when query already using index matching ORDER BY
>
>

I can't find the reference to descending order cases except my posting.
If  we use an index scan to remove sorts in those cases,backward positioning
and scanning are necessary.

> >     Analysis of the above timings:
> >
> >     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 (who really selects nearly all rows from a 5M row
> >     table?). So choosing the index path  is  correct.  This  will
> >     hurt if someone really selects most of the rows and the index
> >     scan jumps over the disc.  But here the programmer should use
> >     an  unqualified  query  to  perform  a  seqscan  and  do  the
> >     qualification in the frontend application.
>
> Fortunately, the optimizer already does the index selection for us, and
> guesses pretty well if the index or sequential scan is better.  Once we
> implement the above removal of psort(), we will have to change the
> timings because now you have to compare index scan against sequential
> scan AND psort(), because in the index scan situation, you don't need
> the psort(), assuming the ORDER BY matches the index exactly.
>

Let t be a table with 2 indices, index1(key1,key2), index2(key1,key3).
i.e. key1 is common to index1 and index2.

And for the query select * from t where key1>....;

If   PosgreSQL optimizer choose [ index scan on index1 ] we can't remove
sorts from the following query.select * from t where key1>... order by key1,key3;

Similarly if  [ index scan on index2 ] are chosen  we can't remove sorts
from the following query.select * from t where key1>... order by key1,key2;

But in both cases (clever) optimizer can choose another index for scan.

Thanks.

Hiroshi Inoue
Inoue@tpf.co.jp