Curiosity is good, I also did some tests here, with yours and the syntax
suggested by "Achilleas Mantzios" and the results were:
1ST TEST
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
The table has ~930000 rows, query returns ~33000 rows (results from one day)
Column is timestamp without time zone.
There is an index on timestamp_column
The result is the average of running 10 times each SELECT.
SELECT * FROM xxx WHERE (tscol) >= '2007-05-20 00:00:00' AND (tscol) <=
'2008-05-20 23:59:59';
--> .478" (3rd place)
SELECT * FROM xxx WHERE (tscol) BETWEEN '2007-05-20 00:00:00' AND
'2008-05-20 23:59:59';
--> .475" (1st place)
SELECT * FROM xxx WHERE (tscol) >= CAST('2007-05-20' as timestamp) AND
(tscol) < CAST('2008-05-21' as timestamp);
--> .483" (4th place)
SELECT * FROM xxx WHERE (tscol)::date = '2008-05-20'::date;
--> .476" (2nd place)
2ND TEST
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
The table has ~930000 rows, query returns ~196000 rows (results from
five days)
SELECT * FROM xxx WHERE (tscol) >= '2007-05-20 00:00:00' AND (tscol) <=
'2008-05-25 23:59:59';
--> 2.477" (1st place)
SELECT * FROM xxx WHERE (tscol) BETWEEN '2007-05-20 00:00:00' AND
'2008-05-25 23:59:59';
--> 2.540" (4th place)
SELECT * FROM xxx WHERE (tscol) >= CAST('2007-05-20' as timestamp) AND
(tscol) < CAST('2008-05-26' as timestamp);
--> 2.512" (3dr place)
SELECT * FROM xxx WHERE (tscol)::date >= '2008-05-20'::date AND
(tscol)::date <= '2008-05-25'::date;
--> 2.482" (2nd place)
- The 4th SELECT was a surprise, how could it was so fast if it does not
use the index?
- Creating an additional index needs to be studied carefully because our
tables are huge and indexes are already consuming ~1/3 of the size of
tables.
Regards
Roberto Garcia
Jan-Ivar Mellingen wrote:
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.
The table alarmlogg has ~930000 rows, query returns ~260000 rows.
Column alarm_tid is timestamp with time zone.
There is an index on alarm_tid.
select * from alarmlogg where alarm_tid between '2007-05-20 00:00:00'
and '2008-05-20 23:59:59';
--> 152 seconds.
select * from alarmlogg where (alarm_tid >= '2007-05-20 00:00:00') and
(alarm_tid <= '2008-05-20 23:59:59');
--> 151 seconds.
SELECT * FROM alarmlogg WHERE alarm_tid >= CAST('2007-05-20' as
timestamp) AND alarm_tid < CAST('2008-05-21' as timestamp);
--> 301 seconds.
I am using the syntax in the second example in my programs. It has
worked since 8.0.
Regards
Jan-Ivar Mellingen
Roberto Garcia skrev:
We changed it because 8.3 doesn't allow the operator LIKE on
timestamp columns. Your syntax works fine but we weren't used to use
as u do. There weren't any specific reason, only another way to do that.
I think when we read that operator LIKE and timestamp values were
incompatible we assumed that timestamp values couldn't be compared to
any char value, opposed as your syntax is.
We've tried to do "select * from X where <timestamp column> =
'2008-05-20 10:'", expecting that the result would be any minute from
10 o'clock (10:15, 10:30, 10:45, etc) of the specific date, but this
syntax retrieves only 10:00 from that date.
Tks for the new syntax.
Regards
Roberto Garcia
Gregory S. Youngblood wrote:
That's a pretty substantial change. Why did you have to make this
change?
Was it causing syntax errors or to get better performance on those
types of
queries? Actually, now that I think about it, didn't:
select * from X where <timestamp column> between '2008-05-20
00:00:00' and
'2008-05-20 23:59:59' work? I could have sworn I have used that
syntax in 8.2 without having to
arbitrarily cast the arguments... now I'm going to have to go look. :)
Yup, confirmed, 8.2.7 and no casting on the date arguments when I
was using
between.
I'm just curious if there was a specific reason (i.e. better
performance,
better use of indexes, etc.) for your syntax.
Thanks,
Greg
-----Original Message-----
From: pgsql-admin-owner@xxxxxxxxxxxxxx
[mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Roberto Garcia
Sent: Wednesday, June 18, 2008 12:01 PM
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: [ADMIN] Major upgrade advice
Just to mention one issue we had here:
In 8.1 we did this to retrieve all data from a specific date:
SELECT * FROM xxx
WHERE <timestamp_column> LIKE '2008-05-20%'
In 8.3 we had to change to:
SELECT * FROM xxx
WHERE <timestamp_column> >= CAST('2008-05-20' as timestamp) AND
<timestamp_column> < CAST('2008-05-21' as timestamp)
Regards
Roberto Garcia
Roberto Garcia
Banco de Dados, MSc
Fone: (12) 3186-8405
Roberto Garcia
Banco de Dados, MSc
Fone: (12) 3186-8405
--
A luta contra o aquecimento global depende de cada um de nós, faça sua parte, economize recursos naturais.
--
http://www.cptec.inpe.br
http://www.inpe.br
