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: (view raw, whole thread or download thread mbox)
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 
               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 
results in more work than an integer datatype.

Eric Cholet

In response to


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-2017 The PostgreSQL Global Development Group