天文データセンター SQL講習会[データベース構築編] 資料

山内＠天文データセンター

2011年12月 初版 (2012年2月更新)

本講習会の内容

目次

注意

講義

イントロ: なぜRDBMSのスキルが必要か?

背景

自然科学の研究や観測所等での開発において， RDBMSのスキルを身につけておくと， 大規模なデータを効率良く扱えるという点で大きなアドバンテージになる．

おさらい: RDBMSを使う上で最低限の知識

RDBMSってどんなもの?

RDBMSを制御するために使う言語「SQL」

データ型(公式ドキュメントからの引用)

数値データ型

型名 別名 格納サイズ 説明 範囲
SMALLINT INT2 2バイト 狭範囲の整数 -32768から+32767
INTEGER INT4 4バイト 典型的に使用する整数 -2147483648から+2147483647
BIGINT INT8 8バイト 広範囲整数 -9223372036854775808から9223372036854775807
DECIMAL - 可変長 ユーザ指定精度，正確 最大1000桁[訳注:原文ではno limit]
REAL FLOAT4 4バイト 可変精度、不正確 6桁精度
DOUBLE PRECISION FLOAT8 8バイト 可変精度、不正確 15桁精度
SERIAL - 4バイト 自動増分整数 1から2147483647
BIGSERIAL - 8バイト 自動増分整数 1から9223372036854775807

文字型

型名 説明
CHARACTER VARYING(n)，VARCHAR(n) 上限付き可変長
CHARACTER(n)，CHAR(n) 空白でパッドされた固定長
TEXT 制限なし可変長

RDBMSのインデックスとは --- RDBMSは万能ではない

ビュー(View)

OLTPとOLAP

データベースの世界では 「OLTP(On-Line Transaction Processing)」 と 「OLAP(On-Line Analytical Processing)」 という言葉がよく使われる． RDBMSを使ってデータベースを構築し，最高のパフォーマンスを得たいなら， この2つの概念を押さえておく事は重要である． Wikipediaの記述を引用すると，これらは次のように説明される．

何だかわかりにくいが，要するにこういう事である．

さらに，天文の世界で OLTP と OLAP とを説明してみると，このようになる．

このように天文業界では， データベースの検索においては，OLTP・OLAPの両方について 性能が要求される． そのかわり， データベースの更新については，シビアな要求を課せられる事は少ない．

OLTP・OLAPとも，テーブル設計やインデックスをどのように作るかが 重要であるのは言うまでもないが， それぞれの場合でチューニングのアプローチが異なってくる事は 頭に入れておく必要がある． 非常に軽いOLTP(あまり頻繁にSQLを投げない)の場合には， チューニングやハードウェア性能が不十分でも インデックスさえ作ってあれば，大きなテーブルに対しても それなりの検索性能が得られる． しかし，大きなテーブルに対してOLAPを行なう場合は， RDBMSサーバのメモリの使い方の設定や， テーブル登録時のデータの並びも重要になってくる．そして最後には ディスクI/Oの性能でパフォーマンスが決まってしまう． 極端な話をすると，例えば50GBのテーブル1億行すべてを1秒で取り出すには， RDBMSと言えどもやはり50GB/秒のディスクI/Oが必要だという事である．

○参考○

「大規模なカタログを使いたいが，どのようなディスク装置が良いか?」 という質問をよくうける． これは，次の3つの場合によって答えが異なる．

PostgreSQLとデータベース製品の系譜

RDBMSは，よく「商用製品」と「オープンソース製品」との2種類に大別されるが， この分類はそれぞれのRDBMSのソフトウェア的な分類とは 全く異なる事に注意したい．

現在よく利用されるRDBMS製品として， 商用のものでは ORACLE と SQLServer(Microsoft) が， オープンソースでは，MySQL と PostgreSQL が有名であるが， これらの開発の歴史を見てみると，それぞれの製品の血筋は 商用/オープンソースとは無関係という事に気づく．

次の図は，http://data-e-education.com/E107_History_of_RDBMS.html から引用で，RDBMS製品の系譜である．

この図を見ると，PostgreSQL と SQLServer とは INGRES を親とする兄弟である事がわかる． 一方，ORACLE と MySQL はそれぞれ独立に開発されてきた事がわかる．

SQLには「標準SQL規格」というのがあり， RDBMSベンターはこれにできるだけ対応するようにしているが， 標準から離れた機能になると， かなり作法に違いがでてくる． このような作法の違いは，血筋にかなり依存していて， とあるプロの方の話によると， SQLServerとPostgreSQLとは様々な作法の面で似たもの同士であり， この2者間のデータベースやソフトウェアの移植は簡単であるという．

例えば，今回の講習で PostgreSQL を習ったという事で， ある時点まで PostgeSQL を使ってデータベースを開発してきたが， 性能に満足できない場面に遭遇したとしよう． この場合は，途中で SQLServer に乗り換える事も比較的容易であるというわけだ．

天文学データベースにとって嬉しいPostgreSQLの特徴

今回の講習で，「なぜPostgreSQL?」と思われた方もいらっしゃると思うので， PostgreSQL を使うと嬉しいと思う所を紹介する．

天文学データベースにおいては「座標検索」の需要が最も多い． 典型的には， 「座標」と「半径」とを指定する「Radial Search」であり， 発展的にはWCSの計算を含んだ検索も考えられる． ところが，このような座標検索はRDBMSにとってはやや特殊な処理で， データベースを構築する開発者が何らかのアルゴリズムで実装し， チューニングしなければならない． したがって，「座標検索のためのアルゴリズムが実装しやすく， 最適化がしやすい」というのが RDBMS製品を選ぶ上で重要なポイントである．

具体的には，「Radial Search」においては， 天球面上の2点間の角度の計算を高速に行なう必要があるし， WCSの計算においては， WCSTools の関数をSQLから叩くといった事も必要になってくる． このような場合，PostgreSQLのように C言語で簡単にストアド関数が作る事ができれば， 少ない工数で高い性能と様々な機能を実現できる．

このようにみていくと， 開発環境が強力なPostgreSQLは，実に科学用途向きのRDBMSである と言える．

ひとやすみ: 宣伝

2MASS Catalog Server Kit の紹介

2MASS Catalog Server Kit は，PostgreSQL を活用した，拙作の高性能天体カタログDBキットです． 代表的な大カタログ，小カタログをサポートし， パッケージ化されたソフトウェアと丁寧なインストール手順書， 完全なデータセットの提供により， どなたでもご自分のPCに データセンター並の高性能検索エンジンをセットアップできます．

次の天体カタログをサポートしており， 2TB のディスクがあればすべてをインストールできます．

個人の研究，観測所などで，ぜひご活用ください． なお，ユーザ所有のハードディスクへのデータのコピーも承っています (データファイルあるいはDBまるごと)． ご希望の方は，山内までご連絡ください．

パフォーマンスは， http://darts.jaxa.jp/ir/akari/cas/tools/search/sql.html にて， 

SELECT o.*, n.distance
FROM fGetNearbyObjEq('Twomass', 182.045, 2.8797, 10.0) n,
     Twomass o
WHERE n.objID = o.objID
ORDER BY n.distance

のように試して確認できます．

実習

PostgreSQLサーバの初期設定

この講習では，PostgreSQLサーバのインストール直後(yum install あるいはmake install直後) の状態から，本来の性能を発揮できるデータベースができるまでを， ステップバイステップの実習で体験していただきます．

みなさんが実際に使われるOSは，Linuxが多いと思います． たいていのLinuxディストリビューションでは，バイナリパッケージが用意されており， 例えば，Redhat Linux や CentOS の場合には， 

$ su
# yum install postgresql84
# yum install postgresql84-devel
# yum install postgresql84-server

のようにしてインストールできます．この場合は次の所に PostgreSQL の 設定ファイルやデータベースのためのファイルが置かれています (ここで示すディレクトリやファイルのオーナは「postgres」で， 設定ファイルを変更する時は，ユーザ postgres になってから行ないます)． 

/var/lib/pgsql/data/                    ← pgstartup.log 以外の全ファイルはココ
/var/lib/pgsql/data/pg_hda.conf         ← network・user の permission の設定
/var/lib/pgsql/data/postgresql.conf     ← 使用メモリ等の設定(チューニング)

今回の講習で使うマシン(OSはSolaris)では，ソースからインストールしてあるので， PostgreSQL に関するファイルの置き場所は次の場所となっています(下記のディレクトリはユーザ postgres でないと見えないので注意)． 

/usr/local/pgsql/data/                  ← pgstartup.log 以外の全ファイルはココ
/usr/local/pgsql/data/pg_hda.conf       ← network・user の permission の設定
/usr/local/pgsql/data/postgresql.conf   ← 使用メモリ等の設定(チューニング)

データディレクトリ /どこか/pgsql/data を変更したい場合(例えば，/db/pgsql/data/に変更)， Redhat Linux や CentOS の場合には， /etc/sysconfig/pgsql/postgresql に，次のように設定する事ができます． この設定をしておくと，データベースの初期化や PostgreSQLの起動時にディレクトリの指定が不要になります． 

PGDATA=/db/pgsql/data
PGLOG=/db/pgsql/pgstartup.log

/etc/sysconfig/pgsql/postgresqlを設定したら， 次のようにデータディレクトリを初期化します． 

$ su
# /sbin/service postgresql initdb

PostgreSQLサーバの起動・終了は次のとおりです． 

$ su
# /sbin/service postgresql start                ←PostgreSQLサーバ起動
# /sbin/service postgresql stop                 ←PostgreSQLサーバ停止

一方，/etc/sysconfig/ が無い OS の場合には， データディレクトリの初期化や PostgreSQLサーバの起動時に「-D /db/pgsql/data」 のように引数をつけます． 次の例は，データディレクトリの初期化を行ない， PostgreSQLサーバの起動・停止を行なうものです． 

$ su
# pg_ctl initdb -D /db/pgsql/data               ←DBディレクトリ初期化
# pg_ctl start -D /db/pgsql/data                ←PostgreSQLサーバ起動
# pg_ctl stop -D /db/pgsql/data                 ←PostgreSQLサーバ停止

今回の実習では，すでに初期化が済んでいますから， 上記の作業は不要です．

この後，暫定的なメモリ使用量の設定と， PostgreSQLサーバのロール(ユーザの事; 正式には「ROLE」)設定を行ないますが， Redhat Linux や CentOS の場合，この前に少し設定が必要です． まず，デフォルトで有効になっている「SELinux」を無効に設定してください． さらに，外部のクライアントからPostgreSQLサーバに接続したい場合は， 「Firewall」を無効にするか，5432/TCPを空ける設定を行なう必要があります．

Redhat Linux，CentOS，MacOSX での PostgreSQL のセットアップについては， 2MASS Kit のドキュメント に詳細に書いてありますので，参考にしてください．

◎実習の前の準備作業◎

この講習では，シェルは bash を使い， 作業ディレクトリとして /adc/data/ユーザ名/ を使います． 次のようにして，作業ディレクトリを作成し， 今回の講習に関するファイルはそこに置くようにしてください． 

> bash
$ mkdir /adc/data/ユーザ名
$ cd /adc/data/ユーザ名

◎実習A◎

PostgreSQLサーバの起動と停止を行ないます．

今回はSolarisですので，次の手順で PostgreSQLサーバの起動，停止を行なってください． ユーザpostgres になり，pg_ctl を使います． 

$ su - postgres
% pg_ctl start -D /usr/local/pgsql/data/        ←PostgreSQLサーバ起動
% pg_ctl stop -D /usr/local/pgsql/data/         ←PostgreSQLサーバ停止

起動，停止をしたら，次のようにしてPostgreSQLサーバの状態を確認してみましょう． 

% pg_ctl status -D /usr/local/pgsql/data/

◎実習B◎

PostgreSQLのインストール直後(デフォルト)のメモリ使用量の設定は， 非常に保守的であり， 現在のマシンでは本来の性能を発揮できません． 次の手順により，PostgreSQLサーバの 暫定的なメモリ使用量の設定を行なってください．

ユーザpostgresになり， /usr/local/pgsql/data/postgresql.conf をエディタで開きます． 

$ su postgres
% vi /usr/local/pgsql/data/postgresql.conf

postgresql.conf を開いたら，次のパラメータを探し，値を変更して ファイルを上書き保存してエディタを終了してください． 

shared_buffers = 128MB
work_mem = 512MB
maintenance_work_mem = 128MB
checkpoint_segments = 20
effective_cache_size = 256MB

これらのパラメータは，主にPostgreSQLサーバのメモリの使用量を設定するものです． このうち重要なのは， work_mem と shared_buffers です．

work_mem は OLAP の場合に非常に効くパラメータであり， 多くのレコードについてソート，ジョイン，ストアド関数を使った検索を 行なう場合には，大きくとる必要があります．

shared_buffers は OLTP の場合に重要なパラメータであり， データベースの内容のキャッシュ等に使われます． ディスクの内容はOSによってもメモリ上にキャッシュされるので， PostgreSQL サーバを読み取り専用として使い， かつメモリ消費の多い他ソフトウェアを動かさないのであれば， それほど気にしなくても良いパラメータです．

以上の設定により， かなり巨大なテーブル(例えば1億行)であっても， データベースのデキが良く，まっとうなSQLステートメントを書けば，たいていの場合には PostgreSQLサーバは本来の検索性能を出してくれます．

なお，実際の運用で期待した性能が出ない場合，Let's postgresの PostgreSQLのトラブルシュートとチューニング も活用してください．

◎実習C◎

PostgreSQLサーバのロールの設定を行ないます．
(参考:公式ドキュメント「データベースロールと権限」)

次のように，ユーザpostgres になり， PostgreSQLサーバを起動し，PostgreSQLのターミナル「psql」でログインします． 

$ su - postgres
% pg_ctl start -D /usr/local/pgsql/data/
% psql

さて，これでPostgreSQLサーバのスーパーユーザである「postgres」でログインできました． デフォルトの設定では，パスワードなしでいきなり 「postgres」でログインできるようになっていますので， これからまっとうな設定に変更していきます．

ここで，次のようにして，ロール設定をします． 

postgres=# ALTER ROLE postgres PASSWORD 'postgres';
postgres=# CREATE ROLE admin LOGIN PASSWORD 'admin';
postgres=# CREATE ROLE guest LOGIN PASSWORD 'guest';
postgres=# \q

1行目では，PostgreSQLサーバのスーパーユーザである「postgres」 のパスワードを設定し，2行目，3行目ではユーザ「admin」「guest」を 作成しました．それぞれデータベースの作成者，利用者を想定しています．
今回は練習なので，パスワードを 'postgres' 'admin' 'guest' と設定しました．この部分は状況に応じてセキュアに設定してください．

次に，/usr/local/pgsql/data/pg_hba.conf をエディタで開いて， クライアントのPostgreSQLサーバへの接続にはパスワード認証を必要とする 設定を行ないます． 

$ su postgres
% vi /usr/local/pgsql/data/pg_hba.conf

次のように，各行の末尾の「trust」のところを「md5」 に書き換えます． 

local   all         all                               md5
host    all         all         127.0.0.1/32          md5
host    all         all         ::1/128               md5
                                                      ↑ココ

書き換えたら，PostgreSQLサーバを再起動します． 

$ su - postgres
% pg_ctl stop -D /usr/local/pgsql/data/
% pg_ctl start -D /usr/local/pgsql/data/
% exit
$

以上の設定で，パスワード認証により， UNIXの任意のユーザから，任意の PostgreSQL ロールで ログインできます． ロール「postgres」として PostgreSQL サーバにログインしてみましょう． 

$ psql -U postgres
Password for user postgres: xxxxxxxx
psql (8.4.8)
Type "help" for help.

postgres=#

データベースの作成

いよいよデータベースを作成します． ここで言う「データベース」とは， テーブル，ビュー，関数などの入れ物の事です．
(参考: 公式ドキュメント「データベース管理」

◎実習◎

次のようにして，データベースを作成してください． データベースの作成は通常，postgres ロールで行ないます． 

$ psql -U postgres
Password for user postgres: xxxxxxxx
psql (8.4.8)
Type "help" for help.

postgres=# CREATE DATABASE "LESSON";
CREATE DATABASE

データベースが作られたかどうかを確認しましょう． データベースの一覧は「\l」(バックスラッシュとエル)コマンドを使います． 

postgres=# \l 
                              List of databases
   Name    |  Owner   | Encoding | Collation | Ctype |   Access privileges   
-----------+----------+----------+-----------+-------+-----------------------
 LESSON    | postgres | EUC_JP   | ja        | ja    | 
 postgres  | postgres | EUC_JP   | ja        | ja    | 
 template0 | postgres | EUC_JP   | ja        | ja    | =c/postgres
                                                     : postgres=CTc/postgres
 template1 | postgres | EUC_JP   | ja        | ja    | =c/postgres
                                                     : postgres=CTc/postgres
(4 rows)

LESSON 以外に， postgres，template0，template1 というデータベースがありますが，これらは管理(今ログインしているのが postgresデータベース)や， サーバの内部実装のためのものです．

「あかり」IRC全天カタログの登録

このあたりで，psql の使い方を説明したいところですが， その前に，天体カタログをテーブルに登録しておきましょう．

◎実習◎

共有ディレクトリ /data/ にある，akariirc_all.db.txt.gz， akariirc_create_table.sql を，作業ディレクトリ(/adc/data/ユーザ名/)にコピーし，次のようにして テーブル「akariirc」を作成し， ファイルからテーブルへ内容をコピーします．

今度は，psqlには「admin」ロールでログインします． 「postgres」ロールでテーブルを作成する事もできますが， 何でもできてしまう「postgres」ロールを使い続けると内部実装用のデータベースを 壊してしまうリスクがあるため，ロールを分けています． 

$ cd /adc/data/ユーザ名/
$ cp /data/akari* .
$ gunzip akariirc_all.db.txt.gz
$ psql -U admin LESSON
Password for user admin: xxxxxx
psql (8.4.8)
Type "help" for help.

LESSON=> \i akariirc_create_table.sql
psql:akariirc_create_table.sql:38: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "akariirc_pkey" for table "akariirc"
psql:akariirc_create_table.sql:38: NOTICE:  CREATE TABLE / UNIQUE will create implicit index "akariirc_objname" for table "akariirc"
LESSON=> \dt
(テーブルの一覧が表示されます)
LESSON=> \d akariirc
(テーブル情報が表示されます)
LESSON=> \copy akariirc FROM akariirc_all.db.txt WITH NULL AS 'NULL'
(LOG: や HINT: が出るかもしれないが無視してOK．少し時間がかかります)
LESSON=> VACUUM ANALYZE;
(警告が出ますが，無視してOK．)

最後のVACUUMコマンドは， これまでのデータベースへの変更を 完全に反映させるものです． C言語のストリームで言うところの，fflush() に相当するものと思えば良いでしょう．

PostgreSQLの標準ターミナル psql の使い方

天体カタログが登録できたところで， PostgreSQLの標準ターミナル psql の使い方をざっとみていきましょう．
(参考: 公式ドキュメント「psql」)

◎実習A◎

これから，psqlでどんな形式で表が表示できるかをみていきます．

同様に，admin でデータベース LESSON にログインしてください． デフォルトでは PAGER が more になっている環境では， 次のように less に切り替えると良いでしょう． 

> bash
$ PAGER=less
$ export PAGER
$ psql -U admin LESSON
Password for user admin: xxxxxx
psql (8.4.8)
Type "help" for help.

次のように，「あかり」IRCカタログの先頭4行を取り出し， 一旦，ログアウトしてください． 

LESSON=> SELECT objid,ra,dec FROM akariirc LIMIT 4;
   objid   |         ra          |        dec        
-----------+---------------------+-------------------
 200000001 | 0.00038349999057939 | -5.49428600000346
 200000002 | 0.00112950260338502 |  61.2523077534135
 200000003 |  0.0021638354650677 |  62.0123126670265
 200000004 | 0.00341071429238591 |  31.5321020000186
(4 rows)
LESSON=> \q

今度は psql に「-H」オプションをつけ， 同じ SELECT 文を実行してみてください． 

$ psql -H -U admin LESSON
Password for user admin: 
psql (8.4.8)
Type "help" for help.

LESSON=> SELECT objid,ra,dec FROM akariirc LIMIT 4;

今度は，表が HTML で表示されました．次の場合も試してみてください． csv形式，LaTeXの形式で表示されるはずです． 

$ psql -A -F , -U admin LESSON

$ psql -P format=latex -U admin LESSON

このように，psql は表示方法だけでも実に様々なオプションがあります．

◎実習B◎

「あかり」IRC全天カタログの登録では， 「\i」「\copy」というコマンドを使いました． これらは，psql の特別なコマンドであり「メタコマンド」と呼ばれます．

ここでは，非常に良く使うメタコマンドを試してみましょう．

「\dt」や「\df」などは， 「\dt+」「\df+」のように 「+」をつけるとより詳細な情報を表示できます．

◎実習C◎

psqlでは，「\set」により変数を定義することができます． 変数を参照する時は，「:変数名」とします． 定義した変数をSQLステートメントの中で使うと便利です． 例を示します: 

LESSON=> \set objid_begin 200023186
LESSON=> \set objid_end 200023189
LESSON=> SELECT * FROM AkariIrc WHERE objid BETWEEN :objid_begin AND :objid_end;

さらに詳しい情報は， 公式ドキュメント をご覧ください．

インデックスを作成する

RDBMSを使う上で最低限の知識 で解説したとおり，RDBMSで本来の検索性能を得るには， インデックスの存在が最も重要です． 先ほど登録した「あかり」IRC全天カタログ用のテーブル (テーブル情報)には プライマリキー(objid)が含まれていますから， 例えば次のような検索では，すでに本来の性能が出るようになっています．
(参考: 公式ドキュメント「インデックス」) 

SELECT * FROM akariirc WHERE objid BETWEEN 200311508 AND 200311511;

次の実習では，プライマリキー以外のインデックスを作ってみます．

◎実習◎

テーブル akariirc には，プリマリキーである「objid」と ユニーク制約が設定されている「objname」以外はインデックスが 作成されていません． psqlの「\timing」コマンドを使って， カラム「ra」が10度程度の範囲を，同じ条件で3回以上SELECTし， 最後の3回の実行時間の中間値をメモしてください．
テストに使う SQLステートメントは，次のようなもので良いでしょう． 

SELECT count(*) FROM akariirc WHERE ra BETWEEN 140 AND 150;

その後，カラム「ra」に対して次のようにインデックスを作成してください (VACUUMではたくさん警告が出ますが，無視してかまいません)． 

CREATE INDEX akariirc_ra ON akariirc (ra);
VACUUM ANALYZE;

「akariirc_ra」はインデックスの名前で，わかりやすいものなら 何でも良いですが，「テーブル名+カラム名」としておいた方が無難です．

インデックス作成後，同様の条件で3回以上SELECTし， 最後の3回の実行時間の中間値をメモしてください． 速くなっていましたか?

なお，インデックス作成後の「VACUUM ANALYZE;」は 必ず必要です． CREATE INDEX の後にVACUUMを忘れると， せっかく作ったインデックスが使われない事があります． 常に忘れないように注意しましょう．

テーブルを作成する

「あかり」FIS全天カタログのためのテーブルを作成してみます．
(参考: 公式ドキュメント「データ定義」)

テーブルの定義は最も単純には次の形をとります． 

CREATE TABLE テーブル名 (
        カラム名1       型      [NOT NULL],
        カラム名2       型      [NOT NULL],
        :
        :
        [CONSTRAINT 制約名 PRIMARY KEY(カラム名)],
        [CONSTRAINT 制約名 UNIQUE (カラム名)],
);

「NOT NULL」を書くと，そのカラムには NULL は存在できなくなります．

「CONSTRAINT…PRIMARY KEY()」は このテーブルのプライマリキーを定義しています． プライマリキーはテーブルの行を特定するのに使われるものですから， NULL値や重複は許されません． もしデータ登録時に重複したセル値やNULLが発見されると， エラーとして登録はリジェクトされます． もちろん，インデックスはデータ登録と同時に作成されます．

「CONSTRAINT…UNIQUE()」は 指定されたカラムがユニークである(重複が無い)と定義しています． なお，複数のNULL値は重複とはみなされません． もしデータ登録時に重複したセル値が発見されると， エラーとして登録はリジェクトされます． インデックスはデータ登録と同時に作成されます．

「CONSTRAINT…」の部分は， 「テーブル制約」呼ばれます．列制約というのもありますが， 時間の関係で，ここでは取り上げません．

PRIMARY KEYやUNIQUE制約は， 次のようにテーブルの作成，内容の登録後に設定する事もできます． 

ALTER TABLE テーブル名 ADD CONSTRAINT 制約名 PRIMARY KEY (カラム名);
ALTER TABLE テーブル名 ADD CONSTRAINT 制約名 UNIQUE (カラム名);

非常に大きなテーブル(例えば1億行)の場合， 後からプライマリキーを設定すると速度の面で有利です．

さて，テーブル定義を書く時に一番やっかいなのは， 「NOT NULL」の設定です． テーブルの定義を作る前に，元のデータファイルで

を把握しなければなりません． ここで元ファイルが独自のバイナリ形式だったりするとさらに面倒です． ドキュメントを読んでも情報が不十分な場合があり， 他の人が書いたバイナリファイル読み取り用コードを 参考にしなければならないような事もよくあります．

もしみなさんが，テーブル形式のデータプロダクトをこの世に出される場合は， NULL値については必ずわかりやすくしていただきたいと思います．

◎課題1◎

この下の表は，AKARI FIS全天カタログのカラム一覧です． 先ほどホームディレクトリにコピーした akariirc_create_table.sql を参考に AKARI FISカタログ用の CREATE TABLEステートメントを作成し， テーブル「akarifis」を登録，データを登録してください． データファイルは，共有ディレクトリ /data/ にある akarifis_all.db.txt.gz を作業ディレクトリにコピーして使ってください．

なお，次のカラム: flux_65, flux_90, flux_140, flux_160, fErr_65, fErr_90, fErr_140, fErr_160, flags_65, flags_90, flags_140, flags_160, mConf_65, mConf_90, mConf_140, mConf_160 については，NULLがありえます．また， データファイル上のNULLの表現は「NULL」です．

CREATE TABLE を実行したら，\d akarifis として， テーブルの内容を確認し，\copy を使って テーブルにデータを登録してください．

なお，この課題ではCREATE INDEXを実行する必要はありません．

nametypeindexunitdescription
objid INTEGER P Primary key (unique identifier) for the 2MASS Kit database. Do not refer this ID in your literatures.
objNameCHARACTER(14) U Source identifier from its J2000 coordinates, following the IAU Recommendations for Nomenclature (2006). The format is HHMMSSS+/-DDMMSS, e.g., 0123456+765432 for a source at (01h23m45.6s, +76d54m32s). The source must be referred to in the literatures by its full name; AKARI-FIS-V1 J0123456+765432.
raDOUBLE PRECISION S deg J2000 Right Ascension of the source position.
decDOUBLE PRECISION S deg J2000 Declination of the source position.
posErrMjREAL arcsec One-sigma error of the source position expressed by an ellipse with Major and Minor axes in arcsec, and Position Angle in degees measured from North to East. In the currently version we give the same value (6.0 arcsec) for all the sources both in the major and minor axis (thus polar-angle is 0.0) based on the statistical analysis in Section 5.5 of the Release Note .
posErrMiREAL arcsec See description of posErrMj.
posErrPAREAL deg See description of posErrMj.
flux_65REAL S Jy Flux density of the source in the four FIS bands in Jansky. In the catalogue the four FIS bands are indicated by their central wavelength as 65, 90, 140 and 160.
Values are given even for the unconfirmed sources as much as possible, though such the data are not guaranteed. If it is not possible to measure the source flux, `NULL' is set.
flux_90REAL S Jy See description of flux_65.
flux_140REAL S Jy See description of flux_65.
flux_160REAL S Jy See description of flux_65.
fErr_65REAL Jy Flux uncertainty of the source flux in Jansky. It is evaluated as the standard deviation of the fluxes measured on the individual scans divided by the root square of the number of measurements (presented in nScanC_65). The error thus only includes relative uncertainty at the measurements. Details of the flux uncertainty are discussed in Section 5.3 of the Release Note .
When it is not possible to calculate the standard deviation, `NULL' is set to this column.
fErr_90REAL Jy Flux uncertainty of the source flux in Jansky.
The value definition is the same as that of fErr_65.
fErr_140REAL Jy Flux uncertainty of the source flux in Jansky.
The value definition is the same as that of fErr_65.
fErr_160REAL Jy Flux uncertainty of the source flux in Jansky.
The value definition is the same as that of fErr_65.
fQual_65SMALLINT S Four level flux quality indicator:
3:  High quality (the source is confirmed and flux is reliable)
2:  The source is confirmed but flux is not reliable (see flags_65)
1:  The source is not confirmed
0:  Not observed (no scan data available)
fQual_90SMALLINT S Four level flux quality indicator.
The value definition is the same as that of fQual_65.
fQual_140SMALLINT S Four level flux quality indicator.
The value definition is the same as that of fQual_65.
fQual_160SMALLINT S Four level flux quality indicator.
The value definition is the same as that of fQual_65.
flags_65SMALLINT A 16-bit flag per band indicating various data condition. In version 1 catalogue three bits are used. The first bit (bit0) is used to indicate the CDS sampling mode. The second bit (bit1) warns the flux of the band is less than a half of the detection limit, and is not reliable. The fourth bit (bit3) tells that the source is possibly false detection due to `side-lobe' effects (see, Section 7.7 of the Release Note). The third bit (bit2) was previously indicated an anomaly which does not exist in the current version, and thus is kept unused. Sources with bit1 or bit3 = 1 have fQual_65 = 2 or less. Other bits are reserved for the future implementation. 
                                   USB ------------- bit ----- LSB
                                       ... 7  6  5  4  3  2  1  0
                                   decimal |  |  |  |  |  |  |  |
   reserved ------------------------- 128 -+  |  |  |  |  |  |  |
   reserved -------------------------- 64 ----+  |  |  |  |  |  |
   reserved -------------------------- 32 -------+  |  |  |  |  |
   reserved -------------------------- 16 ----------+  |  |  |  |
   1: possibly `side-lobe' detection--- 8 -------------+  |  |  |
   not used --------------------------- 4 ----------------+  |  |
   1: Flux too low--------------------- 2 -------------------+  |
   0: Normal mode, 1: CDS mode -------- 1 ----------------------+
`NULL' is set in case of no measurement (fQual_65 = 0).
flags_90SMALLINT A 16-bit flag per band indicating various data condition.
The value definition is the same as that of flags_65.
flags_140SMALLINT A 16-bit flag per band indicating various data condition.
The value definition is the same as that of flags_65.
flags_160SMALLINT A 16-bit flag per band indicating various data condition.
The value definition is the same as that of flags_65.
nScanC_65SMALLINT Number of scans on which the source is properly detected with logEvidence larger than the threshold.
nScanC_90SMALLINT Number of scans on which the source is properly detected with logEvidence larger than the threshold.
nScanC_140SMALLINT Number of scans on which the source is properly detected with logEvidence larger than the threshold.
nScanC_160SMALLINT Number of scans on which the source is properly detected with logEvidence larger than the threshold.
nScanP_65SMALLINT Total number of scans that passed on the source (that possibly observed the source)
nScanP_90SMALLINT Total number of scans that passed on the source (that possibly observed the source)
nScanP_140SMALLINT Total number of scans that passed on the source (that possibly observed the source)
nScanP_160SMALLINT Total number of scans that passed on the source (that possibly observed the source)
mConf_65SMALLINT The month confirmation flags are prepared per band. The value is 1 when the source is observed in the scans separated more than one months (usually an object is visible at every 6 months). This information is independent to hours confirmation and can be 1 even if the source is not confirmed (fQual_65 = 1). Because of the visibility constraint of the AKARI Survey, some sky regions were observed by scans only within a month.
This flag is `NULL' for fQual_65 = 0 sources.
mConf_90SMALLINT The month confirmation flags are prepared per band.
The value definition is the same as that of mConf_65.
mConf_140SMALLINT The month confirmation flags are prepared per band.
The value definition is the same as that of mConf_65.
mConf_160SMALLINT The month confirmation flags are prepared per band.
The value definition is the same as that of mConf_65.
nDensSMALLINT Number of sources in the catalogue within the distance of 5 arcmin from the source. This value is intended to be an indicator of crowdedness of the sky region. Since the source extraction program is tuned so that a unique source is found within 48 arcsec radius, the 5 arcmin radius corresponds to approximately 40 beams.
cxDOUBLE PRECISION C1 Unit vector for (ra,dec)
cyDOUBLE PRECISION C1 Unit vector for (ra,dec)
czDOUBLE PRECISION C1 Unit vector for (ra,dec)

PL/pgSQLでストアド関数を作成する

天文学では座標検索を頻繁に行ないますが，その時に SQL上で座標計算に必要な関数が使えるのと使えないのとでは， 使い勝手にかなり差が出てきます． ここでは，実行速度はそこそこですが，最も手軽に関数を作る事ができる手続き言語 「PL/pgSQL」 でストアド関数を作ってみます．

PL/pgSQLの文法は，だいたい次のようなもので， WHILE文， IF文と 代入の記号「:=」に注意すれば，あとは SQLの文法を継承しているので覚えるべき事は多くありません． もちろん，PL/pgSQLのコード中においても， SQLステートメントで使えるストアド関数やキャストはそのまま使えます．
(参考: 公式ドキュメント「PL/pgSQL」) 

CREATE FUNCTION MY_FUNCTION(arg1 INTEGER, arg2 DOUBLE PRECISION)
  RETURNS DOUBLE PRECISION AS $$
    DECLARE                     -- 変数の宣言はすべて DECLARE の直後に書く
      i INTEGER;
      n INTEGER;
      v DOUBLE PRECISION;
    BEGIN
      n := arg1;                -- 代入は「:=」
      v := arg2;
      i := 0;
      WHILE ( i < n ) LOOP      -- C言語と同じく () は継続条件

        :

      END LOOP;

      IF ( ... ) THEN
        :
      ELSIF ( ... ) THEN
        :
      ELSE
        :
      END IF;

        :

      RETURN v;
    END;
  $$ IMMUTABLE LANGUAGE 'plpgsql';

最後の IMMUTABLE の指定は非常に重要です． これは関数の属性を示すもので，同じ引数が与えられたら， 同じ結果が返るという事を示しています． PostgeSQLサーバのオプティマイザはこの属性をみて，1つのSQLステートメントにおける 関数の実行回数を最小化してくれます． 現在の時刻や乱数を返す場合は，IMMUTABLE とは逆の属性を示す VOLATILE の属性をつけます．

次の課題では，天球上の座標 (ra, dec) を直交座標系の (x,y,z) に変換する関数と， 天球上の2点間の角度を求める関数を作ります．

◎実習◎

PL/pgSQL を使うには，一度だけ「CREATE LANGUAGE」 の実行が必要です．次のように， postgresロールでデータベース LESSON にログインして行なってください． 

$ psql -U postgres LESSON
Password for user postgres: xxxxxxxx
psql (8.4.8)
Type "help" for help.

LESSON=# CREATE LANGUAGE plpgsql;
LESSON=# \q

この後，再度 admin ロールでデータベース LESSON にログインしておいてください．

◎課題2◎

(ra, dec) から単位ベクトル (x, y, z) へは，次の式で変換できます．

x = cos(ra) × cos(dec)
y = sin(ra) × cos(dec)
z = sin(dec)

次に，(ra, dec) から x を求めるストアド関数のコードを示しますから， これに倣って y，z を求めるストアド関数 fEq2Y() と fEq2Z() も 作成してください． 作成ができたら，「\df」で登録されている事を確認し， 作ったストアド関数を動かしてみてください． 

CREATE FUNCTION fEq2X(r DOUBLE PRECISION, d DOUBLE PRECISION)
  RETURNS DOUBLE PRECISION AS $$
    BEGIN
      RETURN cos(radians(r)) * cos(radians(d));
    END;
  $$ IMMUTABLE LANGUAGE 'plpgsql';

radians() は，度からラジアンに変換するためのビルトイン関数です． その逆の変換をする場合は，degrees() を使います．

関数の登録をやりなおしたい場合は，課題4の後の TIPS をご覧ください．

◎課題3◎

天体1の座標を(x1,y1,z1)，天体2の座標を(x2,y2,z2)とし， この天球面上の2点間の角度θは，内積の公式により求める事ができます．

θ = acos( x2*x1 + y2*y1 + z2*z1 )

では，「fDistanceArcminXyz(x1,y1,z1,x2,y2,z2)」を実行すると 2天体間の角度(単位は分角)が返るように， このストアド関数のソースを作り，登録してください．

登録が完了したら，次のようなSQLステートメントで， http://darts.jaxa.jp/ir/akari/cas/tools/search/sql.html で実行した場合と同じ結果が返るかを確認しましょう． 

SELECT fDistanceArcminXyz(1,0,0, 0,1,0);

◎課題4◎

「分角」の角度を「ラジアン」に変換するストアド関数 fArcMin2Rad() を作ってください．

○TIPS○

ストアド関数を登録しなおしたい場合，同じ引数の場合は， 

CREATE OR REPLACE FUNCTION 関数名(...) ...;

とするとストアド関数の内容を上書きできます．

ストアド関数を削除したい場合は，次のようにします． 

DROP FUNCTION 関数名(...);

ここでは，関数の引数は「型」だけを完全に列挙すればOKです．

テーブルの内容を更新する

登録済みテーブルの既存の行を後から変更するには， UPDATE文を使います．UPDATE 文は次のような形をとります．
(公式ドキュメント「データの更新」) 

UPDATE テーブル名
SET column1 = 式1, column2 = 式2, ...
[FROM fromlist]
[WHERE condition]

次の実習では，FROMもWHEREもない， 最も単純な形をとりあげます．

◎実習◎

登録した「akariirc」「akarifis」テーブルには， (ra, dec) の (x, y, z) であるカラム「cx」「cy」「cz」 が埋まっていません．次のようにして確認してみましょう． 

SELECT objid,ra,dec,cx,cy,cz FROM akariirc LIMIT 4;

先ほど作ったストアド関数 fEq2X()，fEq2Y()，fEq2Z() と UPDATE 文 を使って「cx」「cy」「cz」を埋めてみましょう．

その前に，インデックスを削除します(理由はこの後の TIPS2 をご覧ください)． 

DROP INDEX akariirc_ra;

この後，UPDATE文を実行します(しばらく時間がかかります)． 

UPDATE akariirc SET cx = fEq2X(ra,dec), cy = fEq2Y(ra,dec), cz = fEq2Z(ra,dec);

テーブル「akarifis」も同様，「cx」「cy」「cz」を埋めてください． UPDATE を実行したら，SELECT して内容を確認してください．

UPDATE を完了したら，テーブルakariirc について， カラム「ra」「dec」「flux_09」「flux_18」についてインデックスを 作成してください． 複数のインデックスを作成する場合， 「VACUUM ANALYZE;」は最後に一回だけ実行すれば十分です．

○TIPS1○

UPDATE文では，FROM句と WHERE句を伴った形で， ある条件の成立下で 別のテーブルからセルの内容をコピーする事もできます．

次の例では，_rc3_work というテーブルから rc3objall というテーブルにセルの値をコピーするものですが， その条件として「2つのテーブルのカラム『name』の値が等しい」 を課しています． 

UPDATE rc3objall 
SET aliases=o.aliases, ra=o.ra, dec=o.dec, 
    ra_orig=o.ra_orig, dec_orig=o.dec_orig
FROM _rc3_work o
WHERE rc3objall.name = o.name;

○TIPS2○

この実習のように，大量の行に対してUPDATEを行なう時， 更新対象のテーブルに対してCREATE INDEXされている場合は それらのインデックスはすべて削除しておきます． UPDATEが完了したら， 再度CREATE INDEXを行ないます． インデックスを削除せずに大量にUPDATEを行なうと， 相当に時間がかかる事があるので注意しましょう．

講義

おさらい: RDBMSにおける Radial Search の仕組みの考え方

この内容は，ほぼ前回の講習会 の復習である．

天球上のあるポジションからある角度半径内の天体を検索する Radial Search (コーンサーチとも呼ばれる) は，天文屋にとっては最も良く使う検索である． ここでは，RDBMSでどのようにして高速な Radial Search を実現しているかを解説する．

試しに，「あかり」IRCカタログについて，この検索を素直にSQLステートメントに書き下してみる． テーブル中のxyz座標(単位ベクトル; 値の範囲は-1.0から1.0)と， さきほど作成した fDistanceArcminXyz() を使うと， 次のように書ける． 

SELECT *
FROM akariirc
WHERE fdistancearcminxyz(cx,cy,cz,位置x,位置y,位置z)
      <= 角度半径[arcmin]

しかし，このSQLステートメントによる検索では，非常に時間がかかるのは明白である． たとえインデックスが作成されていたとしても， セルの値と検索条件の値との両方に対して演算をしてしまっているので， インデックスを使う事はできない．

このような場合，RDBMS上での実装の定石は，

  1. 必要な行を取りこぼさないように， インデックスを使ってザックリ(少し多めに)検索をする．
  2. 1.の結果から，厳密な条件に合うものだけを取り出す．

のように，検索を2段階に分ける事である．

当然，天文業界ではすでにいくつかの手法が確立しており， 本講義ではそのうちの1つを紹介する．

おさらい: 直交座標系(xyz)を利用したRadial Searchの手法

この手法は，極付近の特別扱いが不要なため単純に実装でき， 小さめの検索半径の時に非常に高速に検索が可能である．

  1. 天体リストのテーブルに，直交座標系の位置(cx,cy,cz;値の範囲は-1.0から1.0)を入れるカラムを追加し，値を登録．
  2. xyzの組み合わせで，インデックスを作成(複合インデックス)．
  3. サーチ半径Rの時，サーチポジションを中心とする 一辺2Rの立方体の中にある天体を，インデックス検索で 取り出し「候補」とする．
  4. 上記「候補」の天体すべてについて，検索位置からの角度を求め， 厳密な条件を満たすものだけ取り出す．

実習

(cx, cy, cz) に複合インデックスを作成する

複合インデックスとは， 複数のカラムに対してまとめてインデックスを作成する方法です．
(参考: 公式ドキュメント「複数列インデックス」)

複合インデックスは， WHERE句に複数のカラムにまたがった検索条件がある場合， つまり典型的には次のような場合に有効に機能します． 

SELECT ...
FROM ...
WHERE (B BETWEEN b_min AND b_max) AND
      (C BETWEEN c_min AND c_max) AND
      (A BETWEEN a_min AND a_max)

通常の単カラムインデックスでは 1つのカラムにしかインデックスが使われませんが， 複合インデックスを作れば， オプティマイザが有効と判断した場合はそれぞれのカラムの検索すべてについて インデックスが使われます．

複合インデックスを作る場合，まず検索する順序を確認する必要があります． 例えば「カラムA」「カラムB」「カラムC」があって，検索時に B→C→A のように絞り込むと最適だと考えられる場合には， その順序 (B,C,A) で複合インデックスを作ります．

注意点として，上記の例を使うと (B,C,A) で複合インデックスを作った場合，カラムCやカラムAだけの検索条件の場合には， インデックスは使われないという事があります．

◎実習◎

この複合インデックスを (cx, cy, cz) に適用します． Radial Searchの場合は，cx,cy,cz のそれぞれの検索優先度は基本的に均等ですから， どの順で複合インデックスを作ってもかまいません． ただし巨大カタログの場合は，どの順番で複合インデックスを作るのが最適かは， テーブルのデータの並びに強く依存します．

次のように，akariirc と akarifis テーブル上の (cx, cy, cz) に対して複合インデックスを作りましょう． 

CREATE INDEX akariirc_xyz ON akariirc (cx,cy,cz);
CREATE INDEX akarifis_xyz ON akarifis (cx,cy,cz);

「VACUUM ANALYZE;」を忘れずに実行してください．

おさらい: 直交座標系を利用した方法で，Radial Searchしてみよう

次の課題も，ほぼ 前回の講習会 の復習です．

◎課題5◎

次の手順に従って， 「あかり」IRCカタログについて， 検索座標(ra=188.580537[deg],dec=6.467703[deg])， から半径 R = 50分角内の天体を検索してください．

  1. 「WHERE (cx BETWEEN xmin AND xmax) AND (cy BETWEEN ...」 の形を使って， 検索座標を中心とする一辺 2R の立方体内にある天体を akariircテーブルから検索し， カラム objid, objname, ra, dec, cx, cy, cz のデータを取得してください．
  2. FROM句でのサブクエリを使って，1. の結果から厳密な条件に合うもののみ 取り出してください．

○ヒント○

Radial Searchを行なうストアド関数を作成する

Radial Searchを行ない，テーブルを返すストアド関数を作ってみます．
(参考: 公式ドキュメント 「複合型」， 「SQL関数」)

◎実習◎

ストアド関数で2カラム以上のテーブルを返すようにするには， 新規に「型」を登録する必要があります． INTEGER のようなプリミティブ型を複数組み合わせたもの (C言語でいう構造体)は，「複合型」あるいは単に「型」と呼びます．

次に作成するストアド関数では，objid，lon，lat，distance のテーブルを返したいので，このように新規に型を作ります． 

CREATE TYPE tObjCelAndDistance AS (objid INTEGER, 
                                   lon DOUBLE PRECISION, lat DOUBLE PRECISION,
                                   distance DOUBLE PRECISION);

「\dT」コマンドで新しい型が登録された事を確認しましょう．

◎課題6◎

テーブル akariirc で，Radial Searchを行なうストアド関数 fAkariIrcGetNearbyObjEq() を作ってください．

今度は，PL/pgSQLではなく SQL を使って作ります． SQL の場合は， ストアド関数の内容定義は SQL の文法の範囲内で作る必要があります．

次に，最初の部分と最後の部分のみヒントを示します． SQL でのストアド関数では，基本的には引数に名前は書かず，型のみ書きます． それぞれの引数は，$1， $2， $3 として SQLステートメントの中で使用します． このストアド関数ではテーブルを返すので， 「RETURNS」の後が「SET OF 型」 となっている事にも注意してください．

「がんばれ!!」の部分を作り， できたら登録してください． 

CREATE FUNCTION fAkariIrcGetNearbyObjEq(DOUBLE PRECISION, DOUBLE PRECISION, DOUBLE PRECISION)
  RETURNS SETOF tObjCelAndDistance
  AS 'SELECT o.*
      FROM (

         がんばれ!!

      ) o
      WHERE o.distance <= $3'
  LANGUAGE 'sql';

関数が登録できたら，ストアド関数を実行してみましょう． 

SELECT * FROM fAkariIrcGetNearbyObjEq(188.580537, 6.467703, 50);

SELECT o.* 
FROM fAkariIrcGetNearbyObjEq(188.580537, 6.467703, 50) n,
     akariirc o
WHERE o.objid = n.objid;

○TIPS○

「どうしてPL/pgSQLで書かないの?」と思った方も少なくないと思います． 実は，Radial Search を行なうストアド関数は PL/pgSQL でも書けます． しかし，SQLとPL/pgSQLはそれぞれメリット，デメリットがあり， うまく使いわける方が手間もかからずパフォーマンス的にも有利です．

まとめると，テーブルを扱わない関数は PL/pgSQL で書き， テーブルに対する処理は，まず SQL で書いてみて， ダメそうなら PL/pgSQL で書くようにすれば良いという事になります．

ひとやすみ: AB等級⇔Jy変換のための関数を作成する

◎課題7◎

AB等級と Jy との関係は次のとおりです．

m_AB = -2.5 × log(Jansky/3631);

PL/pgSQLを使って， AB等級から Jy に変換するストアド関数 fABMag2JY() と， Jy から AB等級に変換するストアド関数 fJy2ABMag() を作成してください．

式インデックスを作る

「あかり」全天カタログでは，フラックスの単位は Jy でした． AB 等級で検索したい場合， インデックスを使うためには検索範囲を Jy に変換してから 範囲を指定する必要があります． 例えば，このような SELECT 文です． 

SELECT objid, flux_09, fjy2abmag(flux_09) AS mag_09
FROM akariirc
WHERE flux_09 < fabmag2jy(13.0);

しかし，RDBMS には「式インデックス」という技があり， これを使うと， 

SELECT objid, flux_09, fjy2abmag(flux_09) AS mag_09
FROM akariirc
WHERE 13.0 < fjy2abmag(flux_09);

とした場合もインデックスを使った高速検索が可能です． 一般的な表現をすると，式インデックスの作成とは，

v = f(カラム1,カラム2,カラム3, ... ,カラムn)

の v に対してインデックスを作成する事，と言えます． 要するに，テーブル上のセルの値から何らかの計算をして， その結果の値に対してインデックスを作るというわけです． SQLステートメントは次の形をとります． 

CREATE INDEX インデックス名 ON テーブル名 (function(カラム名1,カラム名2,...));

さらに，式インデックスを複数組み合わせて「複合式インデックス」 を作る事もできます: 

CREATE INDEX インデックス名 ON テーブル名 (function1(カラム名1,カラム名2,...),function2(カラム名A,カラム名B,...), ...);

上記ではテーブル名の後は function(…) と書いていますが， この部分は四則演算等も含める事ができます． 式インデックスは，ストアド関数の結果に対してインデックスを作る事が多いので， 関数インデックスとも呼ばれます．
(参考: 公式ドキュメント「式に対するインデックス」

◎実習◎

次のSQLステートメントを入力し， 

EXPLAIN
SELECT objid, flux_09, fjy2abmag(flux_09) AS mag_09
FROM akariirc
WHERE 13.0 < fjy2abmag(flux_09);

実行計画の先頭に，インデックスが使われていない事を示す「Seq Scan」 という表示があるのを確認してください．

○TIPS○

EXPLAIN SELECT … ; とすると，その SELECT 文の実行計画の詳細が表示されます． 注目すべきなのは「Seq Scan」「Index Scan」の表示です． 「Seq Scan」の場合は列全体を順にサーチする事を示し， 「Index Scan」はインデックスを使って検索する事を示しています．

ただし，インデックスが作られていても，「Seq Scan」 となる事があります． これはたいていの場合，テーブル全体の行数に対して 引き出す行数が多い時です． PostgreSQLのプランナは，どちらを使うかを状況に応じて 判断します．

◎課題8◎

akariirc テーブルに対し， 

SELECT objid, flux_09, fjy2abmag(flux_09) AS mag_09
FROM akariirc
WHERE 13.0 < fjy2abmag(flux_09);

とした場合も高速に検索できるように，式インデックスを作成し (「VACUUM ANALYZE;」を忘れずに)， インデックスを作る前と後とでパフォーマンスが変化する事と， EXPLAINにより， 検索方式が「Index Scan」に変化する事を確認してください．

○参考○

式インデックスは，上の課題のような素直な使い方のほか， 大規模データにおける検索パフォーマンス改善のための テクニックの1つとして使う事もあります．

このテクニックとは， 式インデックスを使ってインデックスのためのディスクアクセスを最小化する というものです． 例えば，8バイト浮動小数点数値に対するインデックスは， 相対的にディスクを多く使います．したがって， 大規模データになるとディスクアクセスの増加が問題になる事がよくあります． このような場合には，落とせるところまで数値の精度を落として インデックスを作成するという事を考えます． ある場合には4バイト整数値に，ある場合には2バイト整数値にまで 精度を落とせるかもしれません． この時に，式インデックスが活躍するわけです．

先に紹介した 2MASS Catalog Server Kit においても，このテクニックは使われています． 2MASSやUSNO-B1.0のような大規模カタログの場合， 天体の座標は4バイト整数で格納した上で， (x,y,z) に関しては， 2バイト整数値に対してインデックスを作成しています(精度はワーストケースで約9秒角)． これにより，例えば4.7億行の2MASS PSCの場合，(x,y,z)に対するインデックスは 全体でわずか10GBという小容量に抑えています． ここまで小さくできれば，現在の計算機ならRAMディスク上に載せる事ができ， その場合は究極の速度でインデックスを参照できます． 2MASS Kitでは，このような様々なテクニックを駆使して できる限り高いパフォーマンスを狙っています．

「あかり」FISカタログ・IRCカタログでクロスマッチを行なう

Radial Searchを繰り返す方法で， 2つのカタログのクロスマッチをやってみます．

◎課題9◎

akariirc テーブルにて Radial Search を行ない， 見つかった天体のうち，指定された座標に最も近い天体の objid 1つを返す ストアド関数 fAkariIrcGetNearestObjIDEq() を作成してください． ヒントとして，最初と最後の部分のコードを示しておきます． 

CREATE FUNCTION fAkariIrcGetNearestObjIDEq(FLOAT8,FLOAT8,FLOAT8)
  RETURNS INTEGER
  AS 'SELECT 

      がんばれ!!

      LIMIT 1'
  IMMUTABLE LANGUAGE 'sql';

◎実習◎

FISカタログからIRCカタログに対して，半径0.1分角で クロスマッチを行ない，ヒットした件数を求めてください． もし困ったら， 前回の講習会の資料 も参考にしてください．

次のように，一般的には小さい方のカタログを dec でソートします． 「7959 件」ヒットしたら正解です． 

SELECT count(fAkariIrcGetNearestObjIDEq(o.ra, o.dec, 0.1)) 
FROM ( SELECT * FROM akarifis ORDER BY dec ) o;

○TIPS○

実は，ストアド関数を実行する時のオーバヘッドは小さくありません． したがって，実行速度を重視する場合は ストアド関数を何段も重ねるような実装をしてはいけません．

また，RDBMSは「速度」と「柔軟性(汎用性)」との両立を目指したものですが， 両方の要素が飛び抜けて優れているというわけでも無いという事は 頭に入れておく必要があります． 例えば， 上記のようなクロスマッチの手法は「速度面」では常に優れているとは言えません． 特定の場合(例えば，2つのカタログは必ず dec でソートされている場合)における 「速度」だけを目的にするなら専用のソフトウェアを作る方が良いでしょう．

しかし，最近のマルチコアCPUの普及は，RDBMSのようなCPUに負担がかかる汎用ソフトウェアを より後押しするものとなっています．つまり，これまでCPUでの演算に時間がか かり，I/Oが暇だったケースにおいて， マルチスレッドで一気に演算量を増やせる場合は， 全体としてのパフォーマンスアップが見込めるからです．

拙作の論文 の Appendix に，PostgreSQLサーバに対して同時に複数のセッションを張って 「あかりIRC」「2MASS」のクロスマッチをした実験結果があります． 興味がある方はご覧になってみてください．

C言語でストアド関数を作る

講義では，PostgreSQLでは C 言語でストアド関数を作る事も簡単である， と述べました． また，C 言語で書かれたストアド関数は，バイナリモジュール内の ネイティブなプログラムを実行するわけですから， 呼び出し時のオーバヘッドを除いて非常に高速です． 例えば，典型的な Radial Search の場合， 2天体間の角度を求めるストアド関数を PL/pgSQL で書いたものと， C言語で書いたものとを比べると， その速度差は 2 倍程度にもなります．

一方，C 言語のストアド関数でできる事は限られていますし， 文字列を扱ったり C 言語から離れた事をやろうとすると， かなり面倒になってきます． したがって，数値計算を高速化したり， 数値計算のライブラリを使うような用途に限定するのが現実的と言えます．

以下では，実際に fArcMin2Rad() と fDistanceArcminXyz() とを，C言語で作りなおしてみましょう．
(参考: 公式ドキュメント「C言語関数」

◎実習A◎

akariirc テーブルで少し大き目の半径で Radial Search を行ない， 3回以上実行して，実行にかかった時間をメモしてください． 次のような SQL ステートメントで良いでしょう． 

SELECT count(*) FROM fAkariIrcGetNearbyObjEq(0,0,2000);

◎実習B◎

C言語でストアド関数を作る時は，次の手順のとおりにソースを書き始めます．

ここまでは1回だけ書けば良く，この後に関数のためのコードを 次の手順に従って書きます．

関数の引数を受ける時は PG_GETARG_FLOAT8(0) や PG_GETARG_INT32(0) のように書きます． このあたりは，実際のコードを見てもらった方がわかりやすいでしょう．

それでは，実際にコードを書いて，コンパイルしてみましょう． 次のコードを pg_module.c として保存してください． 

#include <postgres.h>
#include <fmgr.h>
#include <math.h>

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(arcmin2rad);
PG_FUNCTION_INFO_V1(distancearcminxyz);
extern Datum arcmin2rad(PG_FUNCTION_ARGS);
extern Datum distancearcminxyz(PG_FUNCTION_ARGS);

Datum arcmin2rad(PG_FUNCTION_ARGS)
{
    float8 v = PG_GETARG_FLOAT8(0);
    PG_RETURN_FLOAT8( 0.0002908882086657216 * v );
}

Datum distancearcminxyz(PG_FUNCTION_ARGS)
{
    float8 mx, my, mz, mxyz;
    float8 ret;

    mx = PG_GETARG_FLOAT8(0);
    my = PG_GETARG_FLOAT8(1);
    mz = PG_GETARG_FLOAT8(2);

    mx *= PG_GETARG_FLOAT8(3);
    my *= PG_GETARG_FLOAT8(4);
    mz *= PG_GETARG_FLOAT8(5);

    mxyz = mx + my + mz;
    if ( 1.0 < mxyz ) mxyz = 1.0;
    else if ( mxyz < -1.0 ) mxyz = -1.0;

    ret = acos( mxyz ) * 3437.7467707849396;
    PG_RETURN_FLOAT8(ret);
}

次のようにして，コンパイルし，モジュールを作ります(Linux の場合は， 「-R…」引数は不要です)． 

$ gcc -Wall -O -fPIC -I/usr/local/pgsql/include/server -c pg_module.c
$ gcc -shared -L/usr/local/lib -R/usr/local/lib -o pg_module.so *.o -lm

ユーザ postgres になり， 次のようにして pg_module.so を設置してください． 

$ su postgres
% cp pg_module.so /usr/local/pgsql/userlib/.
% exit

なお，PostgreSQLインストール直後には /usr/local/pgsql/userlib は無いので root になってディレクトリを作り， オーナを postgres:postgres に変更しておきます．

一旦，PL/pgSQL で書かれたストアド関数を削除します． 

$ psql -U postgres LESSON
Password for user postgres: xxxxxxxx
psql (8.4.8)
Type "help" for help.

LESSON=# DROP FUNCTION fArcMin2Rad(DOUBLE PRECISION);
LESSON=# DROP FUNCTION fDistanceArcminXyz(DOUBLE PRECISION,DOUBLE PRECISION,DOUBLE PRECISION,DOUBLE PRECISION,DOUBLE PRECISION,DOUBLE PRECISION);

その後，C言語で書かれた関数を登録します． なお，C言語のストアド関数の登録は，postgres ロールでしかできない事に 注意してください． 

CREATE FUNCTION fArcMin2Rad(DOUBLE PRECISION) 
       RETURNS DOUBLE PRECISION AS 
       '/usr/local/pgsql/userlib/pg_module.so', 'arcmin2rad' 
       IMMUTABLE LANGUAGE 'c';
CREATE FUNCTION fDistanceArcminXyz(DOUBLE PRECISION,DOUBLE PRECISION,DOUBLE PRECISION,
                                   DOUBLE PRECISION,DOUBLE PRECISION,DOUBLE PRECISION) 
       RETURNS DOUBLE PRECISION AS 
       '/usr/local/pgsql/userlib/pg_module.so', 'distancearcminxyz' 
       IMMUTABLE LANGUAGE 'c';

今度は admin ロールで LESSON データベースにログインして， ストアド関数の動作確認をし，Radial Searchのパフォーマンスを確認しましょう．

講義

PostgreSQLでのテーブルパーティショニング

RDBMSで巨大データを扱う場合， パフォーマンスが問題になる事が多い． こうした問題の1つに，条件によってはテーブルの行数が増えるに従って 検索速度が遅くなっていく事が挙げられる．

上の図は，1つのテーブルに2MASSカタログ(全部で4.7億)のデータを 徐々に登録していって，テーブル内容がオンメモリの状態で 秒間何回 Radial Search ができるかをテストしたものである． 行数が増えるに従ってパフォーマンスは大きく低下していく事がわかる．

このような問題は， すべてのデータを1つのテーブルに入れるのではなく， 何からの基準で複数のテーブルに分割(パーティショニング)すると， 解決できる場合がある．

当然，パーティショニングを行なうと，分割したテーブルの管理をしなければ ならない． パーティショニングの管理手法には， 分割したテーブルを「RDBMSで」マネージする方法と， 「RDBMSの外側(典型的にはWebアプリケーション)で」マネージする方法がある． 後者は，RDBMS製品に依存しないというメリットがあるが， SQL ベースで使っていく場合には，かなり使いづらいものとなる． 前者の場合，メリットデメリットは全く逆の話になってくる． この講習会では，SQL が話の中心なので， パーティショニングをRDBMSでマネージしていく話をとりあげる．

PostgreSQLでテーブルパーティショニングを行なう場合， 「テーブルの継承」と「テーブル定義時のCHECK制約」 を組み合わせて多くのテーブルをマネージする．
(参考: 公式ドキュメント「パーティショニング」)

「テーブルの継承」というのはPostgreSQLの機能で， オブジェクト指向プログラミング言語でいうクラスの継承と似たようなものである． 今の場合，「クラスのメンバ変数」が「カラム」に置き換わったと思えば良い． このテーブルの継承を使うと，「親テーブル」(中身は空で良い)を作っておけば， 再度テーブル定義を書かなくても 親のテーブル定義を引き継いだ「子テーブル」を作る事ができる． しかも，親テーブルに対して SELECT すると， 透過的に子テーブルすべてに対して SELECT された結果が返る．
(参考: 公式ドキュメント「継承」)

「テーブル定義時のCHECK制約」は， 「このテーブルはこれこれの条件に合致したものしか入れるな」 という制限をテーブルの制約として課す機能である．
(参考: 公式ドキュメント「検査制約」)

テーブルパーティショニングでは， データを子テーブルにどのように振り分けるかを決めるのに いくつかの方法があるが， 良く使われるのは「レンジ・パーティショニング」といって， あるカラムの値の範囲ごとに，子テーブルにデータを振り分ける方法である (下の例では deci のレンジを使っている)．

天体カタログの場合，dec ごとに分割するのが最も単純である． テーブルの継承を使って多くの子テーブルを作り， 子テーブルの CHECK制約に，dec の範囲を記述する事になる． この場合， 子テーブル作成時の実際のSQLステートメントは次のようになる (Twomass_j2000iが親テーブルの名前である)． 

CREATE TABLE twomass_j2000i_01
 (CHECK(-745775150 <= deci AND deci < -704928050))
 INHERITS (Twomass_j2000i);

テーブルパーティショニングを使おうとした場合， 初めは誰でも1つのテーブルを単純に dec ごとに分割する事を考えるであろう． しかし，これには大きな問題がある． それは，dec 以外で検索した時に， 全子テーブルをスキャンするためパフォーマンスが出にくいという問題である． 残念なことに，プライマリキーでの検索では， テーブル分割した場合は1つのテーブルに比べてパフォーマンスが劣ってしまう． プライマリキーは，他テーブルとのジョインなど利用する場面が多いので， プライマリキーでの検索が遅いというのは，許容できない．

上記の問題を避けるため，パーティショニングあり/なしの 両者の長所を生かした実装を考える．つまり， パーティショニングをしない大テーブルが1つと， パーティショニングを使う複数の小テーブルを作るわけである．

上の図は，2MASS Kit version 1 で使っているテーブル構造で， 子テーブルには必要なカラムだけを取り出した上で， dec の範囲でテーブルを分割している． ストアド関数は，Radial SearchとRectangular Searchの2種類を用意し， それぞれが専用のテーブルセットにアクセスするようにしている． この時，これらの関数は，必要な子テーブルだけにアクセスするように なっているので，検索速度は非常に高速である． しかも，子テーブルは横の長さが小さく， テーブルセット全体としてもコンパクトなので ディスクへのアクセスも最小化される．

ユーザが使う場合には，図のように (1) ストアド関数で検索， (2) ストアド関数の結果とメインテーブルとを objid(プライマリキー) でジョイン， の2ステップになる．(2) のジョインは，データ登録時のデータの並びさえ気をつければ 非常に高速で，メインテーブルの行数が1億であろうが10億であろうが， パフォーマンスはほとんど変わらない．

Rectangular Searchの方は，先の CHECK 制約と PostgreSQL の Constraint Exclusion(CE) により，かなり簡単に実装ができている． CE は，PostgreSQL の機能で，PostgreSQLサーバは ユーザの親テーブルに対する SQL ステートメントをパースし， 定義された CHECK 制約に従って自動的に必要な子テーブルのみアクセスしてくれる機能である． しかしこの CE には制限があり， それぞれの子テーブルのCHECK制約を判断するのにある程度の時間を要するため， あまりに子テーブルが多いとパフォーマンスが出ない． したがって，子テーブルの個数は100個程度が限度とされる．

一方，Radial Searchのストアド関数では，CE は使っておらず， C言語 + PL/pgSQL で作成したストアド関数内で 検索条件によって必要な子テーブルのみにアクセスするようにしている． これにより，子テーブルの数は1000個程度まで増やす事ができ， CEを使う場合に比べてずっと高速な検索を実現している．

上の図は，子テーブルの個数を 4 種類(184, 368, 736, 1472)の場合で パフォーマンスのテストをしたものである． だいたい子テーブルの個数が 1000 くらいまでは， 分割数を増やすと検索性能が改善できる事がわかる．

なお，2MASS Kit version 2 では，子テーブルの種類は1つになり， CE は一切使わなくなった(すべて自前関数で必要な子テーブルを選択)． これにより，消費するディスク容量が若干小さくなったほか， パフォーマンスも若干改善している．

ずいぶん長くなったが，パーティショニングの話はこれで終わりではない． 天体カタログの検索では「リードオンリーが前提」の設計ができるが， テーブル内容の更新がある場合ではさらにノウハウが必要となると考えられる． より一般的なパーティショニングのノウハウについては， Let's postgresの記事 によくまとまっているので，関心がある方は読まれる事をお勧めする．

参考資料: PostgreSQL Conference 2011 資料， 2011PASP..123.1324Y

実習

画像ファイルの検索

天文学の研究においては，画像ファイルが検索できる事も重要です． 画像ファイルの検索を実現するために， HEALPix による天球分割を用いた手法がよく使われます． しかしここでは，この講習会で紹介した直交座標系による点源の検索手法を応用し， 技術的に単純で，非常に簡単に実装できる画像ファイルの検索手法を紹介します．

ここで紹介する手法は， データ登録が非常に高速に行なえるという大きなメリットがありますが， 次のような制限があります(たいていのケースでは問題にならないと思われます)．

手順は簡単です．

まず，データベース側からみていきましょう． 次の2つのテーブルを作ります． 

CREATE TABLE image_main (
        filename   VARCHAR(64)   NOT NULL,
        md5        CHAR(32)      NOT NULL,
        naxis1     INTEGER       NOT NULL,
        naxis2     INTEGER       NOT NULL,
        CONSTRAINT image_main_pkey PRIMARY KEY(filename)
);

テーブル image_main はファイル管理用のテーブルです． md5，naxis1，naxis2 は必須ではありませんし， 他にファイルに付随する情報を入れてもかまいません． 

CREATE TABLE image_region (
        filename   VARCHAR(64)       NOT NULL REFERENCES image_main (filename),
        rid        INTEGER           NOT NULL,
        ra_cen     DOUBLE PRECISION  NOT NULL,  -- 中心の座標(ra,dec)
        dec_cen    DOUBLE PRECISION  NOT NULL,
        cx_cen     DOUBLE PRECISION  NOT NULL,  -- 中心の座標(x,y,z)
        cy_cen     DOUBLE PRECISION  NOT NULL,
        cz_cen     DOUBLE PRECISION  NOT NULL,
        max_r      DOUBLE PRECISION  NOT NULL,  -- 中心から4つのコーナーまでの角度で最大のもの
        x0         INTEGER           NOT NULL,  -- 領域開始点(ピクセル座標)
        y0         INTEGER           NOT NULL,
        width      INTEGER           NOT NULL,  -- 領域の大きさ(ピクセル座標)
        height     INTEGER           NOT NULL,
        CONSTRAINT image_region_pkey PRIMARY KEY(filename,rid)
);

テーブル image_region は，1つの画像をいくつかに分割し， それぞれの領域の情報を入れるためのテーブルです． rid は領域IDで，各画像ファイルで 0,1,2,... のユニークな数字を与えます．

REFERENCES image_main (filename) は， テーブル image_main のカラム filename を参照するという 「外部キー制約」(列制約の一種)で，上記の場合は image_region にファイル名を登録するとき，すでにテーブル image_main の filename に同じファイル名がなければなりません．
(参考: 公式ドキュメント「外部キー」)

このように，外部キーを使ってデータの重複を最小化するようにテーブルを設計する事を， 正規化 といいます．

テーブルができたら，次のように1つの画像をある角度 (例えば10分角前後)で タイル状に分割し，テーブル image_region に必要な情報を 画像ファイルから作ります． 投影歪みが少ない場合は， 単純に同じピクセルサイズの長方形(正方形)で分割してOKです (これにより天球面上での分割領域の大きさに多少バラツキが出ても，検索で取りこぼす事はありません)．

そして，検索時は次のようにします． まず，テーブル image_region において，max_r の最大値を求めます． 

SELECT max(max_r) FROM image_region;

上記の結果を max_all_r とすると， 次のようにすれば検索が実現します． この例は，検索座標 ra=10，dec=20，検索半径が30分角の場合です． 

SELECT o.filename
FROM image_main o
WHERE EXISTS
 (SELECT 1 FROM fImageRegionGetNearbyCenterPointsEq(10,20,30 + max_all_r) n
  WHERE o.filename = n.filename);

ここで使っているストアド関数 fImageRegionGetNearbyCenterPointsEq() のアルゴリズムは，この講習で使った点源検索の場合と全く同じです． このストアド関数の返り値は，テーブルを返すので新規に型を作ります． 

CREATE TYPE tImageRegionInfoAndDistance AS (filename TEXT, rid INTEGER, 
                            lon_cen DOUBLE PRECISION, lat_cen DOUBLE PRECISION,
                            distance DOUBLE PRECISION);

ストアド関数は，次のように書けば良いでしょう． 

CREATE FUNCTION fImageRegionGetNearbyCenterPointsEq(DOUBLE PRECISION, DOUBLE PRECISION, DOUBLE PRECISION)
  RETURNS SETOF tImageRegionInfoAndDistance
  AS 'SELECT o.*
      FROM (
       SELECT CAST(filename AS TEXT), rid, ra_cen, dec_cen,
              fDistanceArcMinXYZ(cx_cen,cy_cen,cz_cen,
                            fEq2X($1,$2),fEq2Y($1,$2),fEq2Z($1,$2)) AS distance
       FROM image_region
       WHERE (cx_cen BETWEEN fEq2X($1,$2) - fArcMin2Rad($3) AND 
                             fEq2X($1,$2) + fArcMin2Rad($3)) AND 
             (cy_cen BETWEEN fEq2Y($1,$2) - fArcMin2Rad($3) AND 
                             fEq2Y($1,$2) + fArcMin2Rad($3)) AND 
             (cz_cen BETWEEN fEq2Z($1,$2) - fArcMin2Rad($3) AND 
                             fEq2Z($1,$2) + fArcMin2Rad($3))
      ) o
      WHERE o.distance <= $3'
  LANGUAGE 'sql';

テーブルに対するインデックスは次のとおり． 

CREATE INDEX image_region_xyz ON image_region (cx_cen,cy_cen,cz_cen);
CREATE INDEX image_region_max_r ON image_region (max_r);
VACUUM ANALYZE;

◎実習◎

次の手順で画像の検索システムを作ってみましょう． なお，今回は画像の枚数が少ないので， CREATE INDEX は行ないません．

○TIPS○

1 度 × 1 度の画像なら約 4 万枚で全天を覆う事ができます． 全天の画像データの場合，例えば 1つの分割領域の大きさを 10分角とすると， テーブルの行数は150万程度で済むので， データベース構築で苦労する事はないでしょう． しかし検索精度はそれなりで，数分の範囲で「付近の余分な画像ファイル」を ひっかける可能性があります． もちろん，1つの分割領域の大きさを小さくすれば 検索精度は上がりますが，テーブルの行数は増えていき， データ登録に時間がかかったり， 下手をすると検索に時間がかかる事もありえます． 現在の計算機では，RDBMSで扱うテーブルは一般的に 1000万行以下にしておくのが無難でしょう． 検索精度と開発・管理コストとのバランスを考え， 1つの分割領域の大きさを決めてください．

課題の回答例