Message-ID: <46A64475.3040201@andromeiberica.com>
Date: Tue, 24 Jul 2007 20:27:01 +0200
From: Arnau <arnaulist@andromeiberica.com>
Reply-To: arnaulist@andromeiberica.com
User-Agent: Thunderbird 2.0.0.5 (Windows/20070716)
MIME-Version: 1.0
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: index over timestamp not being used
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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