Message-ID: <485BB949.8040504@cptec.inpe.br>
Date: Fri, 20 Jun 2008 11:06:01 -0300
From: Roberto Garcia <roberto.garcia@cptec.inpe.br>
User-Agent: Thunderbird 2.0.0.14 (X11/20080421)
MIME-Version: 1.0
CC: pgsql-admin@postgresql.org
Subject: Re: Major upgrade advice
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>
In-Reply-To: <485B57D6.4040702@alreg.no>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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@postgresql.org
>>> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Roberto Garcia
>>> Sent: Wednesday, June 18, 2008 12:01 PM
>>> Cc: pgsql-admin@postgresql.org
>>> 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