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