Re: Determining which index to create

--On jeudi 22 novembre 2001 11:00 +0900 Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>
wrote:

> 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;

Wow, I am speechless. Sub-second response time, whether the result set
is large or very small. Very impressive. I have resisted pressure to
use Oracle for this application, trusting open source software would do
an equivalent or better job (this table has 140 million records).

Thank you very much.

--
Eric Cholet