Re: index on timestamp performance

--On Wednesday, January 29, 2003 10:45:34 -0800 Stephan Szabo
<sszabo(at)megazone23(dot)bigpanda(dot)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