The following bug has been logged online:
Bug reference: 4224
Logged by: Lawrence Cohan
Email address: lawrencec(at)1shoppingcart(dot)com
PostgreSQL version: 8.2.5
Operating system: red hat 4.1.1-52
Description: issue with LIMIT and ORDER BY
Following queries run FOREVER in PG if an index exists on the "id" column
which is a integer - serial and PKey on the table.
SELECT id FROM orders WHERE merchant_id = xxxxxx ORDER BY id DESC LIMIT 31
-- or 30, 29, 28, 27, 26, 25
SELECT id FROM clients WHERE merchant_id = XXXXXX ORDER BY id LIMIT 3 -- or
With different limits we get different results but the queris are running
forever with DESC as well.
This is a serrios issue as PG documentation says that:
"When using LIMIT, it is important to use an ORDER BY clause that constrains
the result rows into a unique order. Otherwise you will get an unpredictable
subset of the query's rows. You might be asking for the tenth through
twentieth rows, but tenth through twentieth in what ordering?"
pgsql-bugs by date
|Next:||From: hubert depesz lubaczewski||Date: 2008-06-05 18:41:27|
|Subject: Re: BUG #4224: issue with LIMIT and ORDER BY|
|Previous:||From: Simon Riggs||Date: 2008-06-05 17:28:26|
|Subject: Re: BUG #3110: Online Backup introduces Duplicate OIDs|