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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
