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@bitmead.com