Skip site navigation (1) Skip section navigation (2)

Re: index on timestamp performance

From: Eric Cholet <cholet(at)logilune(dot)com>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: index on timestamp performance
Date: 2003-01-29 17:02:04
Message-ID: 214900000.1043859723@shambala.logilune.com (view raw or flat)
Thread:
Lists: pgsql-general

--On Wednesday, January 29, 2003 08:51:30 -0800 Stephan Szabo 
<sszabo(at)megazone23(dot)bigpanda(dot)com> wrote:

> On Wed, 29 Jan 2003, Eric Cholet wrote:
>
>> I have this schema:
>>
>>
>>  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)
>>
>> The performance I'm getting from the index that contains
>> 'date' is much slower than when using the objid index
>> (different queries of course). This is a 10 million row
>> table. Am I right to assume that postgres needs to do
>> more work because it has to convert the dates to some
>> internal (integer?) format?
>
> What does explain (analyze if possible) show for the two queries?

I can't really run two equivalent queries that will each use
a different index. Here's a query that uses the index with 'date'
(output wrapped manually)

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


> It could just be a difference in plans or estimates.

Right, but still I'd like to know whether the timestamp datatype in the 
index
results in more work than an integer datatype.

Thanks,
--
Eric Cholet


In response to

Responses

pgsql-general by date

Next:From: Matthew NuzumDate: 2003-01-29 17:10:37
Subject: Re: monitoring postgres
Previous:From: Jean-Luc LachanceDate: 2003-01-29 17:00:58
Subject: Re: psql command line question..

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group