Re: Determining which index to create - Mailing list pgsql-general

Martijn van Oosterhout wrote:
>
> On Wed, Nov 21, 2001 at 04:09:52PM +0100, Eric Cholet wrote:
> > => explain select * from dico_frs where motid=4742 order by date desc limit
> > 10;
> > NOTICE:  QUERY PLAN:
> >
> > Limit  (cost=46172.25..46172.25 rows=10 width=16)
> >   ->  Sort  (cost=46172.25..46172.25 rows=11382 width=16)
> >         ->  Index Scan using dico_frs_motid_date on dico_frs
> > (cost=0.00..45405.39 rows=11382 width=16)
>
> That's wrong. It doesn't seem to realise that a reverse scan on the index
> would give the right answer. Note that that's only true because you're
> selecting only a single motid. If there were multiple, a reverse scan would
> definitly not be appropriate.

Please try
  select * from dico_frs where motid=4742 order by motid desc,
  date desc limit 10;

regards,
Hiroshi Inoue