On Thu, Jun 05, 2008 at 06:15:29PM +0000, Lawrence Cohan wrote:
> 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
> 1, 2.
> With different limits we get different results but the queris are running
> forever with DESC as well.
my guess is that you:
1. don't have index on merchant_id
2. have a lot of rows in this table
3. very little rows have given merchant_id
you can easily fix the situation with:
create index q on clients (merchant_id, id);
In response to
pgsql-bugs by date
|Next:||From: Lawrence Cohan||Date: 2008-06-05 19:01:47|
|Subject: Re: BUG #4224: issue with LIMIT and ORDER BY|
|Previous:||From: Lawrence Cohan||Date: 2008-06-05 18:15:29|
|Subject: BUG #4224: issue with LIMIT and ORDER BY|