Yes, partitioning by fk_job can significantly improve performance of this update.
And all the SELECTs with definited fk_job can be faster.
All you should check carefully is those SELECTs without definited fk_job.
2015-07-24 17:18 GMT+09:00 twoflower <standa.kurik@xxxxxxxxx>:
Thank you, I will look into those suggestions.
Meanwhile, I started experimenting with partitioning the table into smaller
tables, each holding rows with ID spanning 1 million values and using this
approach, the UPDATE takes 300ms. I have to check if all the SELECTs I am
issuing against the original table keep their performance, but so far it
seems they do, if the appropriate indexes are present on the child tables. I
was worried about the overhead of each query having to go through all
(currently) 58 partition tables, but it seems like it's not that big of a
deal.
--
View this message in context: http://postgresql.nabble.com/The-fastest-way-to-update-thousands-of-rows-in-moderately-sized-table-tp5859144p5859203.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
─repica group──────────────────
▼ポイント×電子マネー×メールで店舗販促に必要な機能を全て提供！
【point+plus】http://www.repica.jp/pointplus/
▼フォローアップメールや外部連携に対応！
【mail solution】http://ms.repica.jp/
▼9年連続シェアNo.1 個人情報漏えい対策ソフト
【P-Pointer】http://ppointer.jp/
▼単月導入可能！AR動画再生アプリ
▼ITビジネスを創造しながら未来を創る
【VARCHAR】http://varchar.co.jp/
───────────────────────────
