Re: [HACKERS] Solution for LIMIT cost estimation

Hannu Krosing wrote:
> As SQL queries are all one-time things you can't be "consistent".
> It's like being able to grab the same set of socks from a bag and
> then trying to devise a strategy for getting them in same order
> without sorting them (i.e. possible but ridiculous)
>
> If you need them in some order, you use ORDER BY, if you don't need
> any order you omit ORDER BY.
>
> > My predudices are caused by what I use PostgreSQL for, which is
> > more favourable to the latter.
>
> Whats wrong with using ORDER BY ?

Only that it's non intuitive that ORDER BY should change the actual
results of a series of LIMIT queries, not just the order. If there are
100 records, and I do 10x LIMIT 10,offset queries one might expect to
get all 100 records. And currently you do (barring something unusual
like a vacuum at an inopportune moment that drastically changes
statistics).

> I can't imagine a set of queries that need to be consistent
> _almost_ all the time, but without any order.
>
> If you really need that kind of behaviour, the right decision is
>to select the rows into a work table that has an additional column
>for preserving order and then do the limit queries from that
>table.

Impractical for stateless web based stuff where keeping state around is
painful if not impossible.

I'm just playing devils advocate here. Changing this is probably not
going to hurt me, I just think it could confuse a lot of people.

> But in that case it is often faster to have an index on said column
> and to do
> WHERE ID BETWEEN OFFSET AND OFFSET+LIMIT
> ORDER BY ID
> than to use LIMIT, more so for large offsets.

--
Chris Bitmead
mailto:chris(at)bitmead(dot)com