Re: Determining which index to create

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.

Not sure how to fix this though.

Functional index?
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Magnetism, electricity and motion are like a three-for-two special offer:
> if you have two of them, the third one comes free.