PostgreSQLのチューニング情報

VACUUM FULL（データベースの最適化）をしても性能が向上しない現象が発生した。
原因は、統計精度が小さすぎることで、統計情報が荒くなって性能が低下するという現象が起きていた。

PostgreSQLのバージョン8.1で、postgresql.confのdefault_statistics_target（統計精度）がデフォルトで10になっていた。（8.4以降はデフォルトが100）
10では大きなテーブルでは統計情報が荒くなる。

対策は、統計精度を拡張することだが、default_statistics_targetを単純に増やしてしまうと
VACUUMの処理時間が長くなる。
よって、性能が低下しているSQLで参照しているテーブル、カラムを個別に拡張することが効果的だと思われる。
設定方法は以下の通り。
　ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET STATISTICS 200;

上記設定が適用されるのはVACUUM実行時。
早くなるのは、統計精度を拡張したカラムを条件としたSELECTのみ。
統計精度が関係ないカラムのSELECTやINSERTには何の影響もない

履歴テーブルから各IDの最新レコードを取得する方法(PostgreSQL)

PostgreSQL8.4 以降に限るが、分析関数を使用すると以下のように取得することができる。
   OVER (PARTITION BY ...) : 区間に分割
   OVER (ORDER BY ...) : 区間ごとに並び替え

select * from (
 select *,
        row_number() over(partition by code order by kaiteiymd desc) as rowNo
 from history
) list
where rowNo = 1

再帰クエリについて（PostgreSQL)

ちょっと再帰クエリについて調べてみたのでメモ。
自身のテーブルを再帰的に参照していくというもの。

========================================================================
WITH RECURSIVE tree_rec(id, parent, name, path, names) as(
    SELECT id, parent, name, array[id] as path,array[name] as names
    FROM tree
    WHERE parent IS NULL
    UNION ALL
    SELECT tree.ID, tree.parent, tree.name
               , tree_rec.path || tree.ID as path
               , tree_rec.names || tree.name as names
    FROM tree
    INNER JOIN tree_rec
    ON tree_rec.id = tree.parent
)

SELECT id, parent, name, path, names
          , array_upper(path, 1) AS level
          , array_to_string(names,'_')
FROM tree_rec
ORDER BY id;

========================================================================

結果、こんな感じ。
左3列がtreeテーブル本来のデータ。

 

 再帰SQLについて

 配列関数について