Performace comparison of indexes over timestamp fields - Mailing list pgsql-performance

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