Re: [HACKERS] Solution for LIMIT cost estimation

From: Don Baccus <dhogaza(at)pacifier(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Philip Warner <pjw(at)rhyme(dot)com(dot)au>
Cc: Chris <chris(at)bitmead(dot)com>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Solution for LIMIT cost estimation
Date: 2000-02-14 19:36:34
Message-ID: 3.0.1.32.20000214113634.01068620@mail.pacifier.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

At 02:27 PM 2/14/00 -0500, Tom Lane wrote:

>CAUTION: if you repeat a query several times with different OFFSET or
>LIMIT values to fetch different portions of the whole result, you will
>find that you get inconsistent results unless you specify an ORDER BY
>condition that is strong enough to ensure that all selected tuples must
>appear in a unique order. Without ORDER BY, the system is free to
>return the tuples in any order it finds convenient

Personally, I would generalize this and leave out the reference to
LIMIT and OFFSET, except perhaps to point out that this is one
particular construct that confuses people.

As PG matures, so will the optimizer and query engine, and people
who've written code that depends on tuples being returned in a
single consistent order might find themselves in for a rude shock.

A well-deserved one (IMO), but still a shock.

The documentation won't stop most people who want to do this
from doing so, they'll test and try to "trick" the system by
taking advantage of behavior that might not be consistent in
future releases.

Still...if it stops even ONE person from doing this, the doc will
do some good.

- 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.

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Lamar Owen 2000-02-14 21:19:16 Release on the 15th?
Previous Message Tom Lane 2000-02-14 19:27:49 Re: [HACKERS] Solution for LIMIT cost estimation