Re: [HACKERS] Solution for LIMIT cost estimation

At 11:07 PM 2/13/00 +1100, Chris wrote:
>Tom Lane wrote:
>> 
>>         SELECT * FROM table WHERE x > 100 ORDER BY x LIMIT 1;
>
>Could it _ever_ be faster to sort the tuples when there is already an
>index that can provide them in sorted order?

That's yet another optimization.  Working on optimizing the execution
of language constructs, whether statement oriented like C or set 
oriented like SQL, is largely a matter of accretion.  Just because
you can make the case with index run fast doesn't mean you don't
want to consider the case where an index isn't available.

I think you're on the losing end of this one, Chris.  In essence
you're asking that the optimizer not take advantage of the
set-oriented, non-ordered nature of SQL queries in order to make
your non-portable code easier to right.

Tom's example is only one instance where fully exploiting the 
fact that values returned by queries are unordered.  I don't think
we can really live with the restriction that queries must always
return tuples in the same order.




- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.