Re: [HACKERS] Solution for LIMIT cost estimation

At 11:12 PM 2/14/00 +1100, Chris wrote:

>So therefore it would be reasonable to optimise a
>limit,offset query with no order by as if there were
>no offset. This would give consistent results, albeit
>it may not choose the best plan. But at least it
>won't hurt anyone.

Why bother?

It will only give consistent results if the table doesn't
change, which is only likely to be during testing if the
table is one which is inserted into, updated, and the like
during production, such as is true of bulletin boards and
the like.

And you normally want to order such queries anyway, by date
or by some ranking criteria.

You are making a mountain out of a molehill, here. Or,
a mountain out of a playa, there's really no molehill
even because your code's broken to begin with.

>If this optimisation is implemented, are we going to
>carefully explain exactly when an ORDER BY clause will
>and won't yield consistent results? Because not just
>any ORDER BY is good enough.

This is already true in SQL as it is, EVEN WITHOUT
LIMIT. If your ORDER BY isn't good enough, each time
you query the db you might get rows back in a different
order.

Even if you grab all the rows and walk through them
yourself, discarding the first OFFSET rows and processing
the LIMIT rows, when you revisit and start over you have
exactly the SAME non-determinancy to worry about.

It has nothing to do with LIMIT, Chris. It really doesn't.

It has to do with your desire to make broken code "work"
in a very limited set of circumstances that don't match
real world conditions often at all.

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