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

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.