Re: Major upgrade advice
От | Roberto Garcia |
---|---|
Тема | Re: Major upgrade advice |
Дата | |
Msg-id | 485BB949.8040504@cptec.inpe.br обсуждение исходный текст |
Ответ на | Re: Major upgrade advice (Jan-Ivar Mellingen <jan-ivar.mellingen@alreg.no>) |
Список | 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
В списке pgsql-admin по дате отправления: