Re: [HACKERS] What about LIMIT in SELECT ?

>
> >         SELECT * FROM tab WHERE key > 'G' ORDER BY key;
> >         results in a sort->indexscan - hmmm.
> >     The  last  one  is  the  query  we  would  need  in  the  web
> >     environment used over a cursor as in the example  above.  But
> >     due  to  the  sort,  the backend selects until the end of the
> >     table, sorts them and then returns only  the  first  20  rows
> >     (out of sorts result).
>
> So you are saying that for this last case the sort was unnecessary? Does
> the backend traverse the index in the correct order to guarantee that
> the tuples are coming out already sorted? Does a hash index give the
> same plan (I would expect a sort->seqscan for a hash index)?

    Good  point!  As  far as I can see, the planner chooses index
    usage only depending on the WHERE clause.  A  hash  index  is
    only  usable  when  the  given  qualification  uses  = on the
    indexed attribute(s).

    If the sortClause exactly matches the indexed  attributes  of
    the  ONE used btree index and all operators request ascending
    order I think the index  scan  already  returns  the  correct
    order. Who know's definitely?

    Addition  to  my  last  posting: ... and if the index scan is
    using a btree index ...


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #