Tuesday, September 30, 2008

Re: [GENERAL] Why Does UPDATE Take So Long?

Doesn't look like that's the problem. I moved my table over to another
Linux box running PG 8.3 and update performance was pretty bad there as
well. In the time that PG 8.3 was struggling with update there I created
a copy of my table on my PG 8.1 machine and inserted all columns with
one containing the altered values I wanted and that took less than two
minutes. Meanwhile, a half-hour later, my PG 8.3 machine was still
thrashing away trying to update that one column that's not even part of
any index..

Something is really wrong with UPDATE in PostgreSQL I think.


Scott Marlowe wrote:
> On Tue, Sep 30, 2008 at 1:00 PM, Bill Thoen <bthoen@gisnet.com> wrote:
>
>> Working with PG 8.1 I'm trying to update a char(4) column, and it's taking a
>> very long time; 15 minutes so far and no end in sight. From the explain, it
>> doesn't seem like it should take that long, and this column is not indexed.
>> Sure, there's 2.7 million records but it only takes a few minutes to scan
>> the whole file. Is there some special overhead I should be aware of with an
>> UPDATE? I VACUUMed and ANALYZEd first, too.
>>
>> Or am I just expecting too much?
>>
>
> The problem is that on older versions of pgsql, the db had to update
> each index for each row updated as well as the rows. The latest
> version, with a low enough fill factor, can update non-indedexed
> fields by using the free space in each page and not have to hit the
> indexes. But on 8.1 you don't get that optimization.
>
>


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

32 comments:

玉鳳 said...

good~ keep sharing with us, please....I will waiting your up date everyday!! Have a nice day........................................

吳怡迪 said...

以簡單的行為愉悅他人的心靈,勝過千人低頭禱告。........................................

GarthThur2218554 said...

真正的朋友不會把友誼掛在嘴巴上..................................................

明宏 said...

thank for share, it is very important . ̄︿ ̄

彥宏志宏 said...

很榮幸能到你的BLOG留言o^~^o........................................

ValarieEdmon靜宜 said...

凡是遇到困擾的問題,不要把它當作可怕的,討厭的,無奈的遭遇,而要把它當作歷練、訓練和幫助。......................................................

文婷 said...

78論壇 A片,成人影片分享 080視訊聊天室 666成人 視訊交友網 xvideo免費影片 視訊美女ws888 6k聊天室辣妹視訊 A片-免費視訊 視訊美女mybank sex888影音視訊聊天室 影音情人趣味 85cc免費影片 視訊激麻館 禁地論壇成人 情色視訊 成人影片情色網 bt成人論壇 18成人avooo 玩美女人試看片 hilive tv視訊妹 免費聊天firework av999免費影片 avdvd無碼影片成人情色 一葉晴貼影片av127 520聊天室 一夜情視訊聊天室 視訊聊天室交友 免費視訊辣妹avdvd一夜情 1元視訊 網愛聊天 250av女優免費影片 免費影音視訊fm358 sex女優王國情色 嘟嘟情人色網 dvd 台灣18網 視訊交友雙贏論壇 色a金激麻館 性愛故事性愛文學 凹凸情欲網 成人視訊happylife 視訊聊天交友mm358 免費視訊辣妹sex女優王國 情色香港論壇 亞洲情色貼圖區 日本 a 片自拍偷拍網站情色小說 免費avi影片下載 台灣情色視訊網 17358 視訊聊天室 日本a片免費下載 情色影片免費觀賞you tube影片下載

恆迪恆迪 said...

如果你不思考未來,你便不會有未來..................................................

07_TeddyF_Silvey0 said...

人有兩眼一舌,是為了觀察倍於說話的緣故。...........................................................................

思姿穎穎 said...

樂觀進取,勇往直前,持之以恒,是克服困難的妙方。 ............................................................

kittycha said...

在你一無所有的時候 是誰在陪伴你 他便是你最重要的人 ............................................................

智宜 said...

Where did you purchase this product?.............................................................

張盈 said...

來幫你衝一下人氣,幫你推推推......................................................................

陳芳 said...

在莫非定律中有項笨蛋定律:「一個組織中的笨蛋,恆大於等於三分之二。」.................................................................

asdas said...

Quality is better than quantity.....................................................................

慧君慧君 said...

一個人的價值,應該看他貢獻了什麼,而不是他取得了什麼............................................................

王名仁 said...

人有兩眼一舌,是為了觀察倍於說話的緣故。............................................................

金正偉 said...

人因夢想而偉大,要堅持自己的理想哦..................................................................

江冠彭珮李佳宏陽筠 said...

相逢即是有緣~~留個言問候一聲,祝您平安順利............................................................

anthonyjensen張anthonyjensen欣虹 said...

人若賺得全世界,賠上自己的靈魂,有什麼益處?......................................................

宋瑞正宋瑞正 said...

沈舟側畔千帆過,病樹前頭萬木春............................................................

林秋萍林秋萍 said...

生命所經歷的折磨愈多,其所產生的奮鬥力愈大。............................................................

王美妹 said...

堅持是為著某種目的或目標,而持續不斷朝向既定方向努力的一種意念。..................................................

淑娟淑娟淑娟 said...

這麼用心的經營你的文章, 當然值得我們留連拜訪的!..................................................................

佳瑩佳瑩 said...

看得見您的用心~~希望這裡愈來愈熱鬧哦~~.................................................................

韋陳富 said...

Learn wisdom by the follies of others.............................................................

豪偉軍樂 said...

出遊不拘名勝,有景就是好的..................................................................

姜鴻林奕宏琬 said...

放棄者不會勝利,勝利者永不放棄。............................................................

沈貞李添睿儀 said...

這不過是滑一跤,並不是死掉而爬不起來了。..................................................

怡涂涂涂均 said...

很棒的分享~~~來留個言囉~~~~............................................................

智柏林婉林亞 said...

在你一無所有的時候 是誰在陪伴你 他便是你最重要的人......................................................................

王辛江淑萍康 said...

學而時習之,不亦樂乎˙ω˙............................................................