From: Arnau <arnaulist@andromeiberica.com>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: index over timestamp not being used
Date: Tue, 24 Jul 2007 20:27:01 +0200
Message-ID: <46A64475.3040201@andromeiberica.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: arnaulist@andromeiberica.com
Subject: Re: index over timestamp not being used
In-Reply-To: <46A64475.3040201@andromeiberica.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox