From: Arnau <arnaulist@andromeiberica.com>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Performace comparison of indexes over timestamp fields
Date: Tue, 22 May 2007 12:39:02 +0200
Message-ID: <4652C846.4020801@andromeiberica.com> (raw)
Hi all,
I have some tables where all the queries that will be executed are
timestamps driven, so it'd be nice to have an index over those fields.
On older versions of PostgreSQL, at least in my experience, queries
on timestamps fields even having indexes where performing quite bad
mainly sequential scans where performed.
Now I have a newer version of PostgreSQL and I've done some tests
comparing the performance of an index over a timestamp field with a
numeric field. To do so, I have the following table:
Table "public.payment_transactions"
Column | Type | Modifiers
----------------+-----------------------------+---------------------------------
transaction_id | character varying(32) | not null
timestamp_in | timestamp without time zone | default now()
credits | integer |
epoch_in | bigint |
epoch_in2 | double precision |
Indexes:
"pk_paytrans_transid" PRIMARY KEY, btree (transaction_id)
"idx_paytrans_epochin" btree (epoch_in)
"idx_paytrans_epochin2" btree (epoch_in2)
"idx_paytrans_timestamp" btree (timestamp_in)
timestamp_in it's the timestamp, epoch_in and epoch_in2 are the epoch
equivalent to timestamp to test how the indexes perform. We have three
different indexes (testing purposes) one over a timestamp field, one
over an int8 and one over a double precision field.
While doing the tests this table has about 100.000 entries.
To test the diferent indexes I have executed the following:
Index over timestamp_in (timestamp)
# explain analyze select * from payment_transactions where timestamp_in
between '2007-02-13'::timestamp and '2007-02-15'::timestamp;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_paytrans_timestamp on payment_transactions
(cost=0.00..1480.24 rows=1698 width=138) (actual time=11.693..310.402
rows=1587 loops=1)
Index Cond: ((timestamp_in >= '2007-02-13 00:00:00'::timestamp
without time zone) AND (timestamp_in <= '2007-02-15 00:00:00'::timestamp
without time zone))
Total runtime: 318.328 ms
(3 rows)
Index over epoch_in (int8)
# explain analyze select * from payment_transactions where epoch_in
between extract( epoch from '2007-02-13'::date )::int8 and extract(
epoch from '2007-02-15'::date )::int8;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_paytrans_epochin on payment_transactions
(cost=0.00..1483.24 rows=1698 width=138) (actual time=34.369..114.943
rows=1587 loops=1)
Index Cond: ((epoch_in >= 1171321200::bigint) AND (epoch_in <=
1171494000::bigint))
Total runtime: 120.804 ms
(3 rows)
Index over epoch_in (double precision)
# explain analyze select * from payment_transactions where epoch_in2
between extract( epoch from '2007-02-13'::date ) and extract( epoch from
'2007-02-15'::date );
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_paytrans_epochin2 on payment_transactions
(cost=0.00..1479.24 rows=1698 width=138) (actual time=26.115..51.357
rows=1587 loops=1)
Index Cond: ((epoch_in2 >= 1171321200::double precision) AND
(epoch_in2 <= 1171494000::double precision))
Total runtime: 57.065 ms
(3 rows)
As you can see the time difference are very big
Timestamp: 318.328 ms
int8 index: 120.804 ms
double precision: 57.065 ms
is this normal? am I doing anything wrong?
As rule of thumb is better to store epochs than timestamps?
Thank you very much
--
Arnau
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: arnaulist@andromeiberica.com
Subject: Re: Performace comparison of indexes over timestamp fields
In-Reply-To: <4652C846.4020801@andromeiberica.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox