Re: Problem with indexes, LIMIT, ORDER BY ... DESC

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