pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex over timestamp not being used
6+ messages / 4 participants
[nested] [flat]
* index over timestamp not being used
@ 2007-07-24 18:27 Arnau <arnaulist@andromeiberica.com>
2007-07-24 19:24 ` Re: index over timestamp not being used Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Arnau @ 2007-07-24 18:27 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: index over timestamp not being used
2007-07-24 18:27 index over timestamp not being used Arnau <arnaulist@andromeiberica.com>
@ 2007-07-24 19:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-24 19:31 ` Re: index over timestamp not being used Arnau <arnaulist@andromeiberica.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2007-07-24 19:24 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; +Cc: pgsql-performance
Arnau <arnaulist@andromeiberica.com> writes:
> timestamp_in | timestamp without time zone | default now()
> SELECT ...
> 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;
to_timestamp() produces timestamp *with* timezone, so your WHERE query
is effectively
t.timestamp_in::timestamptz >= to_timestamp('20070101', 'YYYYMMDD')
which doesn't match the index.
The first question you should ask yourself is whether you picked the
right datatype for the column. IMHO timestamp with tz is the more
appropriate choice in the majority of cases.
If you do want to stick with timestamp without tz, you'll need to cast
the result of to_timestamp to that.
Alternatively, do you really need to_timestamp at all? The standard
timestamp input routine won't have any problem with that format:
t.timestamp_in >= '20070101'
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: index over timestamp not being used
2007-07-24 18:27 index over timestamp not being used Arnau <arnaulist@andromeiberica.com>
2007-07-24 19:24 ` Re: index over timestamp not being used Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-07-24 19:31 ` Arnau <arnaulist@andromeiberica.com>
2007-07-24 19:43 ` Re: index over timestamp not being used Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Arnau @ 2007-07-24 19:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi Tom,
>
> Alternatively, do you really need to_timestamp at all? The standard
> timestamp input routine won't have any problem with that format:
> t.timestamp_in >= '20070101'
This is always I think I'm worried, what happens if one day the internal
format in which the DB stores the date/timestamps changes. I mean, if
instead of being stored as YYYYMMDD is stored as DDMMYYYY, should we
have to change all the queries? I thought the
to_char/to_date/to_timestamp functions were intented for this purposes
--
Arnau
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: index over timestamp not being used
2007-07-24 18:27 index over timestamp not being used Arnau <arnaulist@andromeiberica.com>
2007-07-24 19:24 ` Re: index over timestamp not being used Tom Lane <tgl@sss.pgh.pa.us>
2007-07-24 19:31 ` Re: index over timestamp not being used Arnau <arnaulist@andromeiberica.com>
@ 2007-07-24 19:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-25 13:05 ` Re: index over timestamp not being used Mario Weilguni <mweilguni@sime.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2007-07-24 19:43 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; +Cc: pgsql-performance
Arnau <arnaulist@andromeiberica.com> writes:
>> Alternatively, do you really need to_timestamp at all? The standard
>> timestamp input routine won't have any problem with that format:
>> t.timestamp_in >= '20070101'
> This is always I think I'm worried, what happens if one day the internal
> format in which the DB stores the date/timestamps changes. I mean, if
> instead of being stored as YYYYMMDD is stored as DDMMYYYY, should we
> have to change all the queries?
You are confusing internal storage format with the external
representation.
> I thought the
> to_char/to_date/to_timestamp functions were intented for this purposes
No, they're intended for dealing with wacky formats that the regular
input/output routines can't understand or produce.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: index over timestamp not being used
2007-07-24 18:27 index over timestamp not being used Arnau <arnaulist@andromeiberica.com>
2007-07-24 19:24 ` Re: index over timestamp not being used Tom Lane <tgl@sss.pgh.pa.us>
2007-07-24 19:31 ` Re: index over timestamp not being used Arnau <arnaulist@andromeiberica.com>
2007-07-24 19:43 ` Re: index over timestamp not being used Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-07-25 13:05 ` Mario Weilguni <mweilguni@sime.com>
2007-07-25 13:35 ` Re: index over timestamp not being used Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
0 siblings, 1 reply; 6+ messages in thread
From: Mario Weilguni @ 2007-07-25 13:05 UTC (permalink / raw)
To: pgsql-performance
Am Dienstag 24 Juli 2007 schrieb Tom Lane:
> > I thought the
> > to_char/to_date/to_timestamp functions were intented for this purposes
>
> No, they're intended for dealing with wacky formats that the regular
> input/output routines can't understand or produce.
Really? I use them alot, because of possible problems with different date
formats. 20070503 means May 3, 2007 for germans, I don't know what it means
to US citizens, but following the strange logic of having the month first
(8/13/2005) it might mean March 5, 2007 too. Therefore, using to_timestamp
seems to be a safe choice for me, working in any environment regardless of
the "date_style" setting.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: index over timestamp not being used
2007-07-24 18:27 index over timestamp not being used Arnau <arnaulist@andromeiberica.com>
2007-07-24 19:24 ` Re: index over timestamp not being used Tom Lane <tgl@sss.pgh.pa.us>
2007-07-24 19:31 ` Re: index over timestamp not being used Arnau <arnaulist@andromeiberica.com>
2007-07-24 19:43 ` Re: index over timestamp not being used Tom Lane <tgl@sss.pgh.pa.us>
2007-07-25 13:05 ` Re: index over timestamp not being used Mario Weilguni <mweilguni@sime.com>
@ 2007-07-25 13:35 ` Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
0 siblings, 0 replies; 6+ messages in thread
From: Ansgar -59cobalt- Wiechers @ 2007-07-25 13:35 UTC (permalink / raw)
To: pgsql-performance
On 2007-07-25 Mario Weilguni wrote:
> Am Dienstag 24 Juli 2007 schrieb Tom Lane:
>>> I thought the to_char/to_date/to_timestamp functions were intented
>>> for this purposes
>>
>> No, they're intended for dealing with wacky formats that the regular
>> input/output routines can't understand or produce.
>
> Really? I use them alot, because of possible problems with different
> date formats. 20070503 means May 3, 2007 for germans,
Actually, no. 20070503 is the condensed form of the ISO international
calendar date format (see ISO 8601). German formats would be 03.05.2007
or 3. Mai 2007.
Regards
Ansgar Wiechers
--
"The Mac OS X kernel should never panic because, when it does, it
seriously inconveniences the user."
--http://developer.apple.com/technotes/tn2004/tn2118.html
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-07-25 13:35 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-24 18:27 index over timestamp not being used Arnau <arnaulist@andromeiberica.com>
2007-07-24 19:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-24 19:31 ` Arnau <arnaulist@andromeiberica.com>
2007-07-24 19:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-25 13:05 ` Mario Weilguni <mweilguni@sime.com>
2007-07-25 13:35 ` Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
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