Re: Determining which index to create

--On jeudi 22 novembre 2001 01:04 +1100 Martijn van Oosterhout
<kleptog(at)svana(dot)org> wrote:

> On Wed, Nov 21, 2001 at 12:53:09PM +0100, Eric Cholet wrote:
>> I should have mentionned I tried that, but it isn't being used:
>>
>> => \d dico_frs_motid_date
>> Index "dico_frs_motid_date"
>> Attribute | Type
>> -----------+--------------------------
>> motid | integer
>> date | timestamp with time zone
>> btree
>>
>> => explain select * from dico_frs where motid=4742 order by date desc
>> limit 10;
>> NOTICE: QUERY PLAN:
>>
>> Limit (cost=0.00..17591.91 rows=10 width=16)
>> -> Index Scan Backward using dico_frs_date on dico_frs
>> (cost=0.00..20023641.63 rows=11382 width=16)
>
> Well, it is doing the scan backwards, which is good. But it's not using
> the index. If you drop dico_frs_date index, does it do it then?

=> 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)

It's a bit better but still quite long, depending on how many rows for a
particular motid.
Dropping the "desc" in the "order by date" clause makes things much faster,
but I need the
results in reverse chronological order!

> Oh, and what version of postgres was this again?

=> select version();
version
---------------------------------------------------------------------
PostgreSQL 7.1.3 on i386-unknown-freebsd4.4, compiled by GCC 2.95.3
(1 row)

Thanks for your help,
--
Eric Cholet