pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMajor upgrade advice
13+ messages / 7 participants
[nested] [flat]
* Major upgrade advice
@ 2008-06-18 17:21 Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 3 replies; 13+ messages in thread
From: Steve Crawford @ 2008-06-18 17:21 UTC (permalink / raw)
To: pgsql-admin
I'm getting ready to tackle another upgrade: 7.4.6 -> 8.3.3. The
preliminaries (development/testing) look good but I'd like
suggestions/warnings/comments on:
1. Removing OIDs on user tables.
I don't see any dump or restore options to remove OIDs. Am I better off
modifying the dump to alter the "SET default_with_oids = true;" lines or
going through all the user tables in advance and running "ALTER TABLE
... SET WITHOUT OIDS"? Other options?
2. Alter encoding from C to utf8.
Cheers,
Steve
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Major upgrade advice
@ 2008-06-18 17:28 Joshua D. Drake <jd@commandprompt.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
2 siblings, 0 replies; 13+ messages in thread
From: Joshua D. Drake @ 2008-06-18 17:28 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-admin
On Wed, 2008-06-18 at 10:21 -0700, Steve Crawford wrote:
> I'm getting ready to tackle another upgrade: 7.4.6 -> 8.3.3. The
> preliminaries (development/testing) look good but I'd like
> suggestions/warnings/comments on:
>
> 1. Removing OIDs on user tables.
>
> I don't see any dump or restore options to remove OIDs. Am I better off
> modifying the dump to alter the "SET default_with_oids = true;" lines or
> going through all the user tables in advance and running "ALTER TABLE
> ... SET WITHOUT OIDS"? Other options?
If the table has oids, pg_dump is going to grab them and restore them as
such. If you are assured that you don't need OIDs I would drop the oid
columns from the user tables before the upgrade.
>
> 2. Alter encoding from C to utf8.
Very good chance the dump will not load without going through a cleanup
with iconv.
Joshua D. Drake
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Major upgrade advice
@ 2008-06-18 18:00 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
2 siblings, 1 reply; 13+ messages in thread
From: Achilleas Mantzios @ 2008-06-18 18:00 UTC (permalink / raw)
To: pgsql-admin; +Cc: Steve Crawford <scrawford@pinpointresearch.com>
Στις Wednesday 18 June 2008 20:21:45 ο/η Steve Crawford έγραψε:
> I'm getting ready to tackle another upgrade: 7.4.6 -> 8.3.3. The
> preliminaries (development/testing) look good but I'd like
> suggestions/warnings/comments on:
>
> 1. Removing OIDs on user tables.
>
> I don't see any dump or restore options to remove OIDs. Am I better off
> modifying the dump to alter the "SET default_with_oids = true;" lines or
> going through all the user tables in advance and running "ALTER TABLE
> ... SET WITHOUT OIDS"? Other options?
pg_dump by default ommits OIDs.
However why do you want to completely remove OID functionality?
space usage?
>
> 2. Alter encoding from C to utf8.
there is a script hanging around to do just that.
(i miss it at the moment)
>
> Cheers,
> Steve
>
>
>
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Major upgrade advice
@ 2008-06-18 18:24 Steve Crawford <scrawford@pinpointresearch.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 13+ messages in thread
From: Steve Crawford @ 2008-06-18 18:24 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin
Achilleas Mantzios wrote:
> pg_dump by default ommits OIDs.
> However why do you want to completely remove OID functionality?
> space usage?
>
It doesn't backup the OIDs themselves, but it does set "with oids" if
the table had them. Per docs
(http://www.postgresql.org/docs/8.3/static/runtime-config-compatible.html):
"The use of OIDs in user tables is considered deprecated, so most
installations should leave this variable disabled. Applications that
require OIDs for a particular table should specify WITH OIDS when
creating the table. This variable can be enabled for compatibility with
old applications that do not follow this behavior."
I want to incorporate current recommended practice with the upgrade. Per
longstanding recommendation, we don't use OIDs. Saving 4-bytes/row on
millions of rows is nice, too.
Cheers,
Steve
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Major upgrade advice
@ 2008-06-18 19:00 Roberto Garcia <roberto.garcia@cptec.inpe.br>
parent: Steve Crawford <scrawford@pinpointresearch.com>
2 siblings, 2 replies; 13+ messages in thread
From: Roberto Garcia @ 2008-06-18 19:00 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
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
Steve Crawford wrote:
> I'm getting ready to tackle another upgrade: 7.4.6 -> 8.3.3. The
> preliminaries (development/testing) look good but I'd like
> suggestions/warnings/comments on:
>
> 1. Removing OIDs on user tables.
>
> I don't see any dump or restore options to remove OIDs. Am I better
> off modifying the dump to alter the "SET default_with_oids = true;"
> lines or going through all the user tables in advance and running
> "ALTER TABLE ... SET WITHOUT OIDS"? Other options?
>
> 2. Alter encoding from C to utf8.
>
> Cheers,
> Steve
>
>
>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Major upgrade advice
@ 2008-06-19 18:56 Roberto Garcia <roberto.garcia@cptec.inpe.br>
parent: Roberto Garcia <roberto.garcia@cptec.inpe.br>
1 sibling, 2 replies; 13+ messages in thread
From: Roberto Garcia @ 2008-06-19 18:56 UTC (permalink / raw)
To: pgsql-admin
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
--
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Major upgrade advice
@ 2008-06-20 07:05 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Roberto Garcia <roberto.garcia@cptec.inpe.br>
1 sibling, 1 reply; 13+ messages in thread
From: Achilleas Mantzios @ 2008-06-20 07:05 UTC (permalink / raw)
To: pgsql-admin; +Cc: Roberto Garcia <roberto.garcia@cptec.inpe.br>
Why not simply,
SELECT * FROM xxx WHERE <timestamp_column>::date = '2008-05-20'::date;
Στις Thursday 19 June 2008 21:56:09 ο/η Roberto Garcia έγραψε:
> 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
> --
> 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
>
>
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Major upgrade advice
@ 2008-06-20 07:10 Jan-Ivar Mellingen <jan-ivar.mellingen@alreg.no>
parent: Roberto Garcia <roberto.garcia@cptec.inpe.br>
1 sibling, 2 replies; 13+ messages in thread
From: Jan-Ivar Mellingen @ 2008-06-20 07:10 UTC (permalink / raw)
To: Roberto Garcia <roberto.garcia@cptec.inpe.br>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Major upgrade advice
@ 2008-06-20 13:26 Roberto Garcia <roberto.garcia@cptec.inpe.br>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Roberto Garcia @ 2008-06-20 13:26 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin
We have an index on the time_stamp column, if the format of argument is
different from the format the index was created it is not used, then
performance is decreased because a sequential scan is done instead of an
index scan.
Achilleas Mantzios wrote:
> Why not simply,
> SELECT * FROM xxx WHERE <timestamp_column>::date = '2008-05-20'::date;
>
> Στις Thursday 19 June 2008 21:56:09 ο/η Roberto Garcia έγραψε:
>
>> 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
>> --
>> 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
>>
>>
>>
>
>
>
>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Major upgrade advice
@ 2008-06-20 13:44 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Roberto Garcia <roberto.garcia@cptec.inpe.br>
0 siblings, 0 replies; 13+ messages in thread
From: Achilleas Mantzios @ 2008-06-20 13:44 UTC (permalink / raw)
To: Roberto Garcia <roberto.garcia@cptec.inpe.br>; +Cc: pgsql-admin
Στις Friday 20 June 2008 16:26:19 ο/η Roberto Garcia έγραψε:
> We have an index on the time_stamp column, if the format of argument is
> different from the format the index was created it is not used, then
> performance is decreased because a sequential scan is done instead of an
> index scan.
Then create an additional index like
CREATE INDEX tablename_tscol_date on tablename (date(tscol));
where tscol is timestamp
>
> Achilleas Mantzios wrote:
> > Why not simply,
> > SELECT * FROM xxx WHERE <timestamp_column>::date = '2008-05-20'::date;
> >
> > Στις Thursday 19 June 2008 21:56:09 ο/η Roberto Garcia έγραψε:
> >
> >> 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
> >> --
> >> 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
> >>
> >>
> >>
> >
> >
> >
> >
>
>
> 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.
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Major upgrade advice
@ 2008-06-20 14:06 Roberto Garcia <roberto.garcia@cptec.inpe.br>
parent: Jan-Ivar Mellingen <jan-ivar.mellingen@alreg.no>
1 sibling, 0 replies; 13+ messages in thread
From: Roberto Garcia @ 2008-06-20 14:06 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Major upgrade advice
@ 2008-06-20 14:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jan-Ivar Mellingen <jan-ivar.mellingen@alreg.no>
1 sibling, 0 replies; 13+ messages in thread
From: Tom Lane @ 2008-06-20 14:24 UTC (permalink / raw)
To: Jan-Ivar Mellingen <jan-ivar.mellingen@alreg.no>; +Cc: Roberto Garcia <roberto.garcia@cptec.inpe.br>; pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Major upgrade advice
@ 2008-06-28 19:27 Peter Koczan <pjkoczan@gmail.com>
parent: Roberto Garcia <roberto.garcia@cptec.inpe.br>
1 sibling, 0 replies; 13+ messages in thread
From: Peter Koczan @ 2008-06-28 19:27 UTC (permalink / raw)
To: Roberto Garcia <roberto.garcia@cptec.inpe.br>; +Cc: pgsql-admin@postgresql.org, "Steve Crawford" <scrawford@pinpointresearch.com>
On Wed, Jun 18, 2008 at 2:00 PM, Roberto Garcia
<roberto.garcia@cptec.inpe.br> wrote:
> 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)
Also, don't forget that bareword numbers don't automatically cast to
text anymore.
For instance, assuming col is a text type (char, varchar, text), the query
select * from table where col = 1000
Will throw an error in 8.3 whereas it will work under pre-8.3
releases. You can one either of the following to make it work.
select * from table where col = 1000::text
select * from table where col = '1000'
I've been bitten by that bug a few times.
Peter
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2008-06-28 19:27 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-18 17:21 Major upgrade advice Steve Crawford <scrawford@pinpointresearch.com>
2008-06-18 17:28 ` Joshua D. Drake <jd@commandprompt.com>
2008-06-18 18:00 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2008-06-18 18:24 ` Steve Crawford <scrawford@pinpointresearch.com>
2008-06-18 19:00 ` Roberto Garcia <roberto.garcia@cptec.inpe.br>
2008-06-19 18:56 ` Roberto Garcia <roberto.garcia@cptec.inpe.br>
2008-06-20 07:05 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2008-06-20 13:26 ` Roberto Garcia <roberto.garcia@cptec.inpe.br>
2008-06-20 13:44 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2008-06-20 07:10 ` Jan-Ivar Mellingen <jan-ivar.mellingen@alreg.no>
2008-06-20 14:06 ` Roberto Garcia <roberto.garcia@cptec.inpe.br>
2008-06-20 14:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-28 19:27 ` Peter Koczan <pjkoczan@gmail.com>
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