From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jan-Ivar Mellingen <jan-ivar.mellingen@alreg.no>
Cc: Roberto Garcia <roberto.garcia@cptec.inpe.br>
Cc: pgsql-admin@postgresql.org
Subject: Re: Major upgrade advice
Date: Fri, 20 Jun 2008 10:24:09 -0400
Message-ID: <16600.1213971849@sss.pgh.pa.us> (raw)
In-Reply-To: <485B57D6.4040702@alreg.no>
References: <48594429.7040001@pinpointresearch.com>
<48595B63.4080000@cptec.inpe.br>
<!&!AAAAAAAAAAAYAAAAAAAAAN6d+lQFliBLjc3jO6z0BSQigQAAEAAAANXsptp1/mNJkA+ZsX3eKygBAAAAAA==@tcscs.com>
<485AABC9.3020807@cptec.inpe.br>
<485B57D6.4040702@alreg.no>
Jan-Ivar Mellingen <jan-ivar.mellingen@alreg.no> writes:
> I got curious and did a few tests on a 8.3.3 database on my laptop.
> The 3 different queries all worked, but one took twice as long.
> SELECT * FROM alarmlogg WHERE alarm_tid >= CAST('2007-05-20' as
> timestamp) AND alarm_tid < CAST('2008-05-21' as timestamp);
> --> 301 seconds.
Unsurprising: a comparison between a timestamp with timezone and
one without involves a timezone conversion, so it's gonna be slow.
It's a bit annoying that the system doesn't have the intelligence
to convert the constants to timestamptz just once; but I think
doing that would require introducing an implicit cast from
timestamp to timestamptz, which might cause surprising behaviors
elsewhere.
regards, tom lane
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, jan-ivar.mellingen@alreg.no, roberto.garcia@cptec.inpe.br
Subject: Re: Major upgrade advice
In-Reply-To: <16600.1213971849@sss.pgh.pa.us>
* 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