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(at)debis(dot)com (Jan Wieck) #