［PostgreSQLウォッチ］第25回開発が進むPostgreSQL 8.2，注目の新機能

石井　達夫

2006.03.13

　今回は開発中のPostgreSQL 8.2の状況を報告する。例によって多くの機能が実装されつつあるが，今回はその中からCE(Constraint Exclusion)，新しいcontrib関数のpg_freespacemap，新しく追加されたSQL2003の集約関数などを紹介する。

CEの改良

　CE(Constraint Exclusion)は，継承を使ってテーブルをパーティショニングし，性能を向上させる技術だ。たとえば販売記録を管理するテーブルを，売り上げ日で1カ月ごとに分割するなどの使い方が考えられる。こうすることによって，テーブルの必要な部分だけをアクセスできるので，ディスクアクセスが減ってパフォーマンスが上がるわけだ(CEについては，本連載の19回と22回を参照されたい)。

　CEはPostgreSQL 8.1ではじめて実装された機能だが，残念ながらCEの恩恵を受けることができるのはSELECTとINSERTだけであり，UPDATEとDELETEは相変わらずすべてのテーブルを走査する必要があった(ただし，INSERTではRULEの定義が必要になる)。8.2では，CEがUPDATEとDELETEにも対応する。

　例を示そう。

　今，ここにsales_logという売上記録用のテーブルがあるとする。このテーブルをCEを使って売り上げ日(sales_date列)の値によって，2006/1/1から2006/1/31 用(sales_log_200601)，2006/2/1から2006/2/28 用(sales_log_200602)，2006/3/1から2006/3/31 用(sales_log_200603)の3つにテーブルパーティショニングするものとする。

test=# \d sales_log

     Table "public.sales_log"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 customer_id | integer | 
 sales_date  | date    | 
 item_id     | integer | 
 price       | integer |

Rules:
r_sales_log_200601 AS
ON INSERT TO sales_log
WHERE new.sales_date >= '2006-01-01'::date AND new.sales_date < '2006-02-01'::date DO INSTEAD INSERT INTO sales_log_200601 (customer_id, sales_date, item_id, price)
VALUES (new.customer_id, new.sales_date, new.item_id, new.price)
r_sales_log_200602 AS
ON INSERT TO sales_log
WHERE new.sales_date >= '2006-02-01'::date AND new.sales_date < '2006-03-01'::date DO INSTEAD INSERT INTO sales_log_200602 (customer_id, sales_date, item_id, price)
VALUES (new.customer_id, new.sales_date, new.item_id, new.price)
r_sales_log_200603 AS
ON INSERT TO sales_log
WHERE new.sales_date >= '2006-03-01'::date AND new.sales_date < '2006-04-01'::date DO INSTEAD INSERT INTO sales_log_200603 (customer_id, sales_date, item_id, price)
VALUES (new.customer_id, new.sales_date, new.item_id, new.price)

　以下は，CEがUPDATEに対応していないときの問い合わせプランである。

test=# EXPLAIN UPDATE sales_log SET price = 200 WHERE sales_date = '2006/2/1';
QUERY PLAN
---------------------------------------------------------------
Append (cost=0.00..121.50 rows=32 width=18)
-> Seq Scan on sales_log (cost=0.00..30.38 rows=8 width=18)
Filter: (sales_date = '2006-02-01'::date)
-> Seq Scan on sales_log_200601 sales_log (cost=0.00..30.38 rows=8 width=18)
Filter: (sales_date = '2006-02-01'::date)
-> Seq Scan on sales_log_200602 sales_log (cost=0.00..30.38 rows=8 width=18)
Filter: (sales_date = '2006-02-01'::date)
-> Seq Scan on sales_log_200603 sales_log (cost=0.00..30.38 rows=8 width=18)
Filter: (sales_date = '2006-02-01'::date)

　ご覧のように，sales_log_200601から200603までのすべてのテーブルをスキャンしてしまっている。

　8.2では，以下のように，sales_log_200601とsales_log_200603がスキャンの対象から外れ，CEが有効に働いていることが分かる。

QUERY PLAN
---------------------------------------------------------------
Append (cost=0.00..60.77 rows=18 width=18)
-> Seq Scan on sales_log (cost=0.00..30.38 rows=8 width=18)
Filter: (sales_date = '2006-02-01'::date)
-> Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
-> Seq Scan on sales_log_200602 sales_log (cost=0.00..30.38 rows=8 width=18)
Filter: (sales_date = '2006-02-01'::date)
-> Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
(9 rows)

　なお，説明に使ったテーブルは筆者の著書「改訂第5版 PostgreSQL完全攻略ガイド」(技術評論社，ISBN4-7741-2687-X)から引用したものである。本書ではCEも含めてPostgreSQL 8.1を詳細に解説しているので，よろしければ参考にされたい。

新しいcontrib "pg_freespacemap"

　FSM(Free Space Map)は，テーブルやインデックスの再利用可能領域を管理するデータ構造で，VACUUMによって共有メモリ上に構築される。データの追加や更新が発生すると，まずFSMを参照し，使用できる領域があれば再利用する仕組みになっている。FSMに登録されていないと領域の再利用が進まず，テーブルやインデックスが肥大化してパフォーマンスが低下する。したがって，FSMがきちんと管理されているかどうかを確認することは重要である。

　従来，その方法としては，VACUUMをVERBOSEオプションで起動してそのメッセージを確認するしかなかったが，今回新しく提供されたcontrib関数のpg_freespacemapを使えば，VACUUMをかけなくてもFSMに関する情報が取得できる。

pg_freespacemapの実行例

　pg_freespacemapはまだ開発中であり，仕様が確定していないところがある。今回ご紹介するのは，3月9日に投稿されたパッチに基づいていることをあらかじめお断りしておく。

　pg_freespacemapは関数として実装されているが，使いやすくするためにpg_freespacemapというビューが提供されているので，それを利用してみよう。

test=# SELECT * FROM pg_freespacemap;
 reltablespace | reldatabase | relfilenode | relblocknumber | bytes 
---------------+-------------+-------------+----------------+-------
          1663 |       16384 |       16393 |              0 |   244
          1663 |       16384 |       16393 |              1 |   244
          1663 |       16384 |       16393 |              3 |   244
          1663 |       16384 |       16393 |              4 |   244
          1663 |       16384 |       16393 |              6 |   628
          1663 |       16384 |       16393 |              8 |   244
          1663 |       16384 |       16393 |             11 |   244
          1663 |       16384 |       16393 |             12 |   244
          1663 |       16384 |       16393 |             14 |   372
          1663 |       16384 |       16393 |             22 |   372

[以下省略]

　ここで，reltablespaceはテーブルスペースのOID，reldatabaseはデータベースのOID，relfilenodeはテーブルやインデックスの識別子，relblocknumberはテーブル内のブロックアドレス，bytesはその中の空き領域サイズ(単位:バイト)である。

　この結果からは，たとえばテーブルスペース1663のデータベース16384で，テーブル16393の0ブロックには244バイトの空きがあることが読み取れる。これがFSMが管理している内容である。

pg_freespacemapの表示を見やすくしてみる

　ご覧のように，この表示は決してみやすいものではない。この結果から，データベースやテーブル名を調べるには，pg_database, pg_classといったシステムカタログを検索する必要がある。また，空き領域がブロックアドレス単位で表示されるのは細かすぎることもある。どちらかというと，テーブル単位で空き領域の合計を計算してくれた方が分かりやすいだろう。そこで，以下のようなSQL 文を作ってみた。

pg_freespacemapの表示を見やすくするSQL文

SELECT c.relname, pg_relation_size(c.relname), sum(COALESCE(f.bytes, 8192)) AS free_bytes,
(((1.0 * sum(COALESCE(f.bytes, 8192)))/pg_relation_size(c.relname))*100.0)::NUMERIC(5,2) AS free_percent
FROM pg_freespacemap f, pg_class c, pg_database d WHERE
d.oid = f.reldatabase AND
d.datname = current_database() AND
f.relfilenode = c.relfilenode
GROUP BY 1 ORDER BY 2 DESC;

　実行例を示す。

    relname    | pg_relation_size | free_bytes | free_percent 
---------------+------------------+------------+--------------
 accounts      |         13598720 |     250712 |         1.84
 pg_proc       |           352256 |       2552 |         0.72
 pg_depend     |           245760 |       7088 |         2.88
 pg_attribute  |           212992 |       3408 |         1.60
 pg_statistic  |           114688 |      46572 |        40.61
 history       |            90112 |      33800 |        37.51
 pg_rewrite    |            65536 |       7776 |        11.87
 tellers       |            65536 |      60904 |        92.93
 pg_type       |            40960 |        460 |         1.12
 branches      |            40960 |      36800 |        89.84
 branches_pkey |            40960 |       8192 |        20.00
 pg_class      |            40960 |       3476 |         8.49
 pg_index      |            16384 |       7620 |        46.51
 pg_constraint |             8192 |       7340 |        89.60
(14 rows)

　ここで，relnameはテーブルやインデックスの名前，pg_relation_sizeはその実バイト数，free_byteは空き領域の合計バイト数，free_percentはその比率(%)である(表示は現在接続中のデータベースに限られていることに注意されたい)。

　表示はテーブルサイズの順になるようにしている。

　ちなみに，「sum(COALESCE(f.bytes, 8192))」としているのは，インデックスの場合は丸ごと再利用できるブロックがFSMに登録されており，この場合pg_freespacemapはNULLを返すので，それを補正するためである。

pg_freespacemapの利用方法

　この結果の使い方だが，たとえばfree_percentが少ないものに注目することが考えられる。free_percentはFSMが把握している空き領域のサイズの割合だ。これが少ない場合，実際に削除や更新が少ないのであれば問題ないが，そうでないとすると，VACUUMの頻度が不足して，うまく空き領域がFSMに登録できていないのかもしれない。あるいは，FSMの大きさが少なすぎることも考えられる(FSMの大きさが足りているかどうかはVACUUM VERBOSEコマンドで確認できる)。

　実際にどの位の空き領域がテーブルにあるかは，同じくcontribとして提供されているpgstattupleを使って確認できる。その値と大きくかけ離れていれば，もっと頻繁にVACUUMをかける，FSMを大きくするなどの対策を取るべきであろう。

新しい集約関数の追加

　最新のSQL標準であるSQL:2003で定義されている集約関数が4つ追加された。

var_samp 標本分散(sample variation)

　これは従来PostgreSQLでvarianceと呼んでいたものと同じである。

stddev_pop 母標準偏差(population standard deviation)

stddev_samp 標本標準偏差(sample standard deviation)

　これは従来PostgreSQLでstddevと呼んでいたものと同じである。

var_pop 母分散(population variation)

COPYがコピー行数を返すようになった

　COPYコマンドを実行した後処理された行数が表示されるようになった。

test=# copy tellers to '/tmp/tellers.txt';
COPY 10

今後のPostgreSQL 8.2の開発状況

　現在開発中のPostgreSQL 8.2ではどちらかというと細かな機能や性能改善が多く，現段階ではまだ目に見えて大きな機能はまだコミットされていない。その中で現在パフォーマンス面で大きな改善が期待されているのがソート処理とVACUUMの高速化である。

　前者はすでにかなり実装が進んでおり，場合によっては数倍の高速化がされたとの報告もある。

　後者の方は，更新があったテーブルのブロックアドレスをビットマップとして記録しておき，それをVACUUMが参照することによって，テーブル全体をスキャンしなくて済むようにしようという試みである。実現すれば飛躍的なVACUUM処理の高速化が可能になるはずだ。

　なお，気になるPostgreSQL 8.2のリリース時期だが，夏か秋にβテスト開始，年末か来年の早い時期に正式リリースというのが現段階での計画のようだ。もちろん今後このスケジュールは大きく変わる可能性があるので，参考程度にして欲しい。

■著者紹介

石井達夫（いしい・たつお）氏
国際化の分野を中心にPostgreSQLの開発に参加。一方，本業でもPostgreSQL関連のプロダクトやサービスの企画・開発を統括し，PostgreSQLを使ったビジネスの可能性を追求している。著書に『PostgreSQL完全攻略ガイド』（技術評論社），『PHPxPostgreSQLで作る最強WWWシステム』（技術評論社），『PostgreSQL構築・運用ガイド』（日経BP，共著）などがある。日本PostgreSQLユーザ会会員。