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(at)svana(dot)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.