Re: Problem with indexes, LIMIT, ORDER BY ... DESC - Mailing list pgsql-general

From Ken Williams
Subject Re: Problem with indexes, LIMIT, ORDER BY ... DESC
Date
Msg-id 59A3737C-7AAB-11D6-AEE4-0003936C1626@mathforum.org
Whole thread Raw
In response to Re: Problem with indexes, LIMIT, ORDER BY ... DESC  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
List pgsql-general
On Saturday, June 8, 2002, at 02:41  AM, Stephan Szabo wrote:

> On Fri, 7 Jun 2002, Ken Williams wrote:
>
>> ========================================================================
>> ==
>> =
>> announce=# explain select date from foo where date < '06/08/2001
>> 23:59' and code = 'FOO' order by code, date DESC limit 1;
>> NOTICE:  QUERY PLAN:
>>
>> Limit  (cost=24397.98..24397.98 rows=1 width=20)
>>    ->  Sort  (cost=24397.98..24397.98 rows=6355 width=20)
>>          ->  Index Scan using foo_code_date on foo
>> (cost=0.00..23996.55 rows=6355 width=20)
>> ========================================================================
>> ==
>> =
>>
>> What can I do to improve this?
>
> I'd suggest trying:  order by code DESC, date DESC.
> Otherwise the index order and sort order aren't exactly alike.  In this
> case there's only one code value so we can see that it shouldn't matter
> but I doubt the optimizer knows that.

Aha!  That was the problem - in my head I meant for the "DESC"
to apply to both "ORDER BY" fields, but I forgot that it only
applies one field at a time.  So I can do this:

================================================================
announce=# explain select date from foo where date <
'2000-06-02' and code='FOO' order by code desc, date desc limit
1;
NOTICE:  QUERY PLAN:

Limit  (cost=0.00..3.90 rows=1 width=20)
   ->  Index Scan Backward using foo_code_date on trades
(cost=0.00..10373.82 rows=2663 width=20)
================================================================

Thanks!

  -Ken


pgsql-general by date:

Previous
From: Doug Fields
Date:
Subject: Re: Non-linear Performance
Next
From: Uros Gruber
Date:
Subject: How to start without password