象と戯れ

 | 

2009-02-24空気を読まずにPostgreSQLのを高速化する10のポイント

23:17 | 空気を読まずにPostgreSQLのを高速化する10のポイント - 象と戯れ を含むブックマーク

空気を読まずにPostgreSQLのを高速化する10のポイント

23:17 | 空気を読まずにPostgreSQLのを高速化する10のポイント - 象と戯れ を含むブックマーク はてなブックマーク - 空気を読まずにPostgreSQLのを高速化する10のポイント - 象と戯れ

no title

だからそんなせまっくるしいところでトンチンカンにdisる暇あるんだったら自分のブログでお好みの議論を書くかさもなきゃ/dev/nullにでも吐けとやんわりと言ってるんだよハゲ。

というわけでw。


1.work_memを増やす

work_memは重要です。これのサイズによってGroupAggがHashAggになったり、MergeJoinがHashJoinになったりします。また、外部ソートかメモリソートかの判断にも使われます。初期値1MBが最近のサーバでは小さすぎるので、8MB~32MBまで考えてみましょう。実際にどのぐらいメモリを使うかはプラン実行時に決まるので、32MB×100connectionだからといって必ず3.2GB使うわけではないです。


2.random_page_costを減らす

意外に知られていなくて効力が大きいのがこれ。インデックスを使うかどうかの判断時にプランナがランダムアクセスのコストの単位として使うわけですが、ディスクキャッシュが効いた状態では4というのは大きすぎるので、1~2ぐらいにしておきましょう。これを放っておくとインデックスが正しく使われないなどの状況が生まれます。


3.DISTINCTの代わりにGROUP BYを使う(<= 8.3)

8.3までですが、DISTINCTは常にソートを伴うGroupAggでした。一方GROUP BYは場合によってはHashAggを使うため、GROUP BYの方が早いケースが多々あります。但し、8.4からはDISTINCTもGROUP BY同様にHashAggを選択するようになったため、この考慮は不要です。


4.正規表現は32個に押さえる

クライアントが生成したやたら長い正規表現を伴うWHERE句には気をつけましょう。PostgreSQLは内部的に32個までの正規表現をメモリ上にキャッシュしますが、それ以降は古いものを捨てるので、結果として評価毎に正規表現をコンパイルしてしまします。正規表現 - 象と戯れ - postgresqlグループ


5.ORの代わりにINを使う(>= 8.2)

IndexScanのIndexCondにORを連ねるより、BitmapScanの方が早いのです。そして、IN句の方がBitmapScanが使われやすいのです。それは、構文を解釈するときにデータ型の判別などでIN句の方が簡単だからです。ということだと思っています。Bitmap Scanの威力 - 象と戯れ - postgresqlグループ


6.可能な限りcharではなくtext型を使う

一般的な理由は

  • 長さのチェックを行うため
  • 末尾に空白を補うため
  • text 型への型変換が行われる機会が多いため

char は varchar や text より遅い - PostgreSQL 雑記 - postgresqlグループ

特に3番目の効果が大きいそうです。


7.PL/pgSQLを避ける

PL/pgSQLは呼び出しだけでSPIの初期化を伴う(と思う)ので、何もしない関数の呼び出しでも遅いです。SPIが使えるおかげでSQLとプログラミング言語の合いの子みたいなことが簡単にできるのですが、例えばテキスト変換処理をするときにそれって要らないわけで。可能であればC関数を書きましょう。マニュアルにも書き方が載っています


8.COPYコマンドを上手に使う。それでだめならpg_bulkloadを使う

大量のデータを一括でロードするときは、COPYコマンドを使います。マニュアルにもあるとおり、このコマンドを使うときにはインデックスや制約を予め外しておきましょう。また、maintenance_work_memを数百MBに設定しましょう。ロードし終わったら改めてインデックスや制約を張ります。

それでもだめなら、コーラック、ではなく、pg_bulkloadを使います。

pg_bulkload: プロジェクト ホームページ

インデックスを張ったままでもデータロードが速いだけでなく、timestamp型のパースを決め打ちしているのでここにかかるCPUコストを節約出来ます。なんだかんだいって末尾カラムに「更新時刻」とかを全てのテーブルに保存している運用は多々見受けられるので、これは効いてくると思います。


9.マニュアルをよく読む

PostgreSQLのマニュアルはよくできているだけでなく、翻訳もすばらしい精度とスピードで行われています。細部にわたるまでよく読めばたいがいのことはわかるはずです。


10.ソースを読む

PostgreSQLはオープンソースです。幸いなことにコアメンバーが注意深くメンテナンスしてくれているおかげで、まずどこの環境でもビルドできますし、コメントが豊富に記述されています。初心者でも読みやすく、実験しやすいわけです。ソースを読めばマニュアルに書いてあることの理解が深まりますし、またマニュアルに書いてないことまでわかります。


要は空気を読まずにソース読めと。

・・・お後が宜しいようで。

追記

PostgreSQLを本当に高速化したい人のための10のポイント | 独り言v6

言及頂いてありがとうございます。

一方umitanuki氏も何も書いてないが、元々internalな部分に造詣が深い氏の場合は個別SQL向けチューニングポイントだろう。

ご指摘の通りですね。しかも自分は参照系がメインです。あと、書こうと思って書いていなかったので追記しますが、今回の趣旨は「他のRDBMSでは使えないTips10」。「RDMBS一般にも言えるが」というのは除きました

L.starL.star2009/02/26 19:02> 他のRDBMSでは使えないTips10
吹いたw 確かにそのとおりですね。個別ポイントを押さえておいていただいたおかげで、私は全体論に終始することが出来ました。

 |