Re: index on timestamp performance

--On Wednesday, January 29, 2003 10:45:34 -0800 Stephan Szabo
<sszabo@megazone23.bigpanda.com> wrote:

>
>> >>  motid  | integer                     | not null
>> >>  objid  | integer                     | not null
>> >>  date   | timestamp without time zone | not null
>> >> Indexes: dico_frs_motid_date btree (motid, date)
>> >>          dico_frs_objid btree (objid)
>> >>

> Well, I was wondering for example, the objid queries you were comparing
> to, were they returning an equivalent number of about 3000 rows from the
> index scan?  I think part of the difference may be that here the plan
> grabs the rows from the index and then sorts them all so the limit doesn't
> actually save you significant time.  For better speed on this particular
> sort of query, you might be better off with an order of :
>  order by motid desc, date desc

Indeed, the speed gain is amazing, thanks for your explanation, I understand
things a little better now. Note to self: next time don't make any
assumptions,
just post the explain analyze!

>> => explain analyze select objid from dico_frs where motid=1247
>>    and date <= '2003-01-29 17:55:17' and date >= '2002-10-29 17:55:17'
>>    order by date desc limit 11;
>>
>>  Limit  (cost=4752.14..4752.17 rows=11 width=12) (actual
>>  time=63.20..63.37 rows=11 loops=1)
>>    ->  Sort  (cost=4752.14..4755.11 rows=1187 width=12) (actual
>> time=63.17..63.23 rows=12 loops=1)
>>          Sort Key: date
>>          ->  Index Scan using dico_frs_motid_date on dico_frs
>> (cost=0.00..4691.50 rows=1187 width=12) (actual time=0.08..41.88
>> rows=2924 loops=1)
>>                Index Cond: ((motid = 1247) AND (date <= '2003-01-29
>> 17:55:17'::timestamp without time zone) AND (date >= '2002-10-29
>> 17:55:17'::timestamp without time zone))
>>  Total runtime: 63.93 msec
>> (6 rows)

--
Eric Cholet