カテゴリー: PostgreSQL

出力パスはE”でエスケープが必要

<カンマ区切り>
COPY (SELECT * FROM table_name) TO E’C:\\data.csv’ DELIMITER ‘,’;
COPY table_name FROM E’C:\\data.csv’ with csv;
<タブ区切り>
COPY (SELECT * FROM table_name) TO E’C:\\data.csv’ DELIMITER E’\t’;
COPY table_name FROM E’C:\\data.csv’

UPDATE target set pass = tab.pass
FROM (SELECT id, pass FROM table) tab
WHERE target.id = tab.id ;

PostgreSQL 9.5からBRIN(Block Range Index)インデックスという
一定範囲のブロックを単位とするインデックスが利用できる

通常のB-treeインデックスは
インデックス対象列の値（キー）とレコード位置（ポインタ）を
各レコード毎に持ち「ID=XXX」のような処理は高速に検索できるが
B-treeインデックスは本体のテーブルの1/3近いサイズで増大となる

BRINインデックスの場合はサイズはごくわずかとなる

PostgreSQLのテーブルのパーティショニング（分割）機能

データ量増加により、何かのキーでテーブルを分けたい場合などに
論理的な1つの大きなテーブルを物理的に小さなパーティションに分ける機能

PostgreSQLでは下記のような手順が必要となる
・分割元となるテーブルの作成
・分割したデータが入るテーブルの作成
・データを分割する関数の作成
・データを分割するトリガを作成

検索は高速になるがトリガで子テーブルに振り分けるため登録が遅くなってしまう

PostgreSQL 8.4以降の場合は統計情報ビューを使って遅いSQL文の特定が可能

SQL の実行回数や実行時間などの統計情報を収集するモジュール「pg_stat_statements」を使用する

この機能は contrib モジュールとして提供されているため「share/contrib/pg_stat_statements.sql」を実行して

別途インストールが必要となる

postgresql.confに下記の設定も必要となる
shared_preload_libraries = ‘pg_stat_statements’
custom_variable_classes = ‘pg_stat_statements’
log_min_duration_statement = 3,000
※log_min_duration_statementパラメータで指定の時間を超えたSQLがあった場合に、
実行時間とログを記録する。上記例では3,000＝3秒に設定している。
まず3秒くらいにしてみてログをチェックしてこれでログが出なくなるようにSQLを改善し、
スローSQLログが出なくなったら、パラメータを2秒、1秒と、徐々に設定して1秒でログが
出なくなれば、パフォーマンスも改善していくはず。

 「pg_stat_statementsビュー」
userid:SQL文を実行したユーザのOID
dbid:SQL文が実行されたデータベースのOID
query:代表的な文の文字列
calls:実行回数
total_time:SQL文の処理に費やした総時間（ミリ秒単位）
rows:SQL文によって取得された、あるいは影響を受けた行の総数
蓄積された情報を一旦リセットしたい場合は下記でリセットできる。
SELECT pg_stat_statements_reset();