index over timestamp not being used - Mailing list pgsql-performance

Hi all,

  I've got the following two tables running on postgresql 8.1.4

              transactions
        Column         |            Type             |   Modifiers
----------------------+-----------------------------+---------------
transaction_id        | character varying(32)       | not null
user_id               | bigint                      | not null
timestamp_in          | timestamp without time zone | default now()
type_id               | integer                     |
technology_id         | integer                     |
Indexes:
    "pk_phusrtrans_transid" PRIMARY KEY, btree (transaction_id)
    "idx_phusrtrans_paytyptech" btree (type_id, technology_id)
    "idx_putrnsctns_tstampin" btree (timestamp_in)



               statistics
        Column         |            Type             |        Modifiers
----------------------+-----------------------------+-------------------
statistic_id          | bigint                      | not null
duration              | bigint                      |
transaction_id        | character varying(32)       |
Indexes:
    "pk_phstat_statid" PRIMARY KEY, btree (statistic_id)
    "idx_phstat_transid" btree (transaction_id)


the idea is to have a summary of how many transactions, duration, and
type for every date. To do so, I've done the following query:


SELECT
   count(t.transaction_id) AS num_transactions
   , SUM(s.duration) AS duration
   , date(t.timestamp_in) as date
   , t.type_id
FROM
  transactions t
  LEFT OUTER JOIN statistics s ON t.transaction_id = s.transaction_id
WHERE
  t.timestamp_in >= to_timestamp('20070101', 'YYYYMMDD')
GROUP BY date, t.type_id;

I think this could be speed up if the index idx_putrnsctns_tstampin
(index over the timestamp) could be used, but I haven't been able to do
it. Any suggestion?

Thanks all
--
Arnau