The following bug has been logged online:
Bug reference: 4224
Logged by: Lawrence Cohan
Email address: lawrencec@1shoppingcart.com
PostgreSQL version: 8.2.5
Operating system: red hat 4.1.1-52
Description: issue with LIMIT and ORDER BY
Details:
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
or
SELECT id FROM clients WHERE merchant_id = XXXXXX ORDER BY id LIMIT 3 -- or
1, 2.
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?"