Skip site navigation (1) Skip section navigation (2)

index over timestamp not being used

From: Arnau <arnaulist(at)andromeiberica(dot)com>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: index over timestamp not being used
Date: 2007-07-24 18:27:01
Message-ID: 46A64475.3040201@andromeiberica.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-performance by date

Next:From: Campbell, LanceDate: 2007-07-24 18:41:22
Subject: Re: Table Statistics with pgAdmin III
Previous:From: Dave PageDate: 2007-07-24 17:49:47
Subject: Re: Table Statistics with pgAdmin III

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group