S-JIS[2003-07-19]

PostgreSQL7.2.1 SQL最適化メモ

SQL実行の際は、PostgreSQLが実行計画を立て、最も効率のよい方法で実行される。
が、SQLの組み方によっては効率の悪い方法が選ばれることがあるので注意。


VACUUM ANALYZE

PostgreSQL(プランナー)が実行計画を立てる際には、統計情報が使われる。
統計情報はVACUUM ANALYZEを実行していないと更新されない。SQLの実行が遅いと思ったら、まず真っ先にVACUUMすべし。


EXPLAIN

SQLを実行する際に プランナーがどのような実行計画を立てるかについては、EXPLAINで確認できる。

検索方法の種類
sequential-scan 全件検索 テーブル内のデータ全てを検索する。データ件数が少ないようなら、INDEXを使うより速い場合もある。
index-scan INDEX検索 もちろん、INDEXを作成しておく必要がある。

ほとんどの場合はseq-scanよりindex-scanの方が速いが、EXPLAINでcostを見てみないと確かなことは分からない。

どちらを使うかはプランナーが統計情報を元に判断するが、実行時パラメータを変えることによって強制することも出来る(しかしこれは最終手段と考えるべき。JDBCの場合、1つのstatementでこのパラメータを入れることが出来るが…)。

SET enable_seqscan TO true;		←seq-scanを行う
SET enable_seqscan TO false;	←seq-scanを行わない
SET enable_seqscan TO DEFAULT;	←seq-scanの設定を元に戻す

in サブクエリー

from A where A.key in (SELECT B.key from B where B条件)

この場合、A.keyという項目にINDEXが作成してあったとしても、INDEXが使われない
inでサブクエリー(括弧内のSELECT文)を指定した場合、検索結果の個数が不定なので(実質的にはサブクエリーの結果が必ず1レコードになるのだとしても)、Aの全件検索になるらしい。

書き換えられるならば、「=」existsを使う方がいい。

ちなみに、inの後ろに値を箇条書きした場合はINDEXが使われる。何十個 書いても!

from A where A.key in ('1','2','3')

= サブクエリー

in サブクエリーのケースで サブクエリーの結果が必ず1レコードになると分かっているならば、「in」を「=」にするのがよい。

from A where A.key = (SELECT B.key from B where B条件)

ただし、サブクエリーの結果が複数になったら「ERROR: More than one tuple returned by a subselect used as an expression.」の実行時エラーになる。あくまでも実行時評価なので、たまたま1件になる場合やEXPLAINではエラーにならない。


exists

in サブクエリーのケースはexistsに置き換えられる。そんなに速くならないかも しれないケド。

from A where exists (SELECT B.key from B where B条件 AND A.key = B.key)

enable_seqscan

in サブクエリーだとseq-scanになってしまってインデックスが使われないので、enable_seqscanをオフにしてみるとどうなるか?

EXPLAIN select * from A where A.key in (select B.key from B where B.cond='1');
Seq Scan on B (cost=0.00..22.50 rows=5 width=37)
           ↓
Seq Scan on B (cost=100000000.00..100000022.50 rows=5 width=37)

↑抜粋。seq-scanのままだが、コストの数値は変わっている。が、逆に変な変化をしている…。
EXPLAIN ANALYZEで実行時間を見てみると、差は無かった。件数が少ないテーブルで試しちゃったせいかもしれないけど…。


複数項目で一致させるサブクエリー

複数の項目で一致させるようなサブクエリーは、以下のように書ける。

where (A,B,C) = (SELECT a,b,c from 〜)

しかし、減らせるものなら減らした方がよい。

select * from foo where (a,b) = (SELECT a,max(b) FROM foo WHERE a='hoge')
						↓
select * from foo where a='hoge' and b = (SELECT max(b) FROM foo WHERE a='hoge')

条件が増えて無駄なように見えるが、コストは少なくなる。


JOIN順

JOINを使って複数のテーブルを使用する場合、先に書かれたテーブルから順に評価されるらしい。プランナーが並べ替えたりはしない!

したがって、件数の少ないテーブルを先に書く方が速くなる。
        ↑(WHERE条件で絞られた後の件数)


サブクエリー撲滅

サブクエリーの方が先に評価されるらしいので、「サブクエリーの結果」から最終的に1件だけ抽出するようなSQL文でも、サブクエリーの検索件数が多い場合には処理が遅くなる。

したがって、サブクエリーをFROM句に(移せるなら)移した方がよい。

from A   where A.key in (select B.key from B where B条件);
			↓
from A,B where A.key = B.key and B条件;

UPDATEの場合は、以下の様に書ける。

update A set 〜 where A.key in (select B.key from B where B条件);
				↓
update A set 〜 where A.key = B.key and B条件;

UPDATEの場合、B条件には「B.cond=1」のように、テーブル名をちゃんと付ける必要がある。UPDATEにはFROM句が無いので、テーブル名「B」が指定できないから。


WHERE条件をまとめる

(A条件 and B条件) or (A条件 and C条件)
		↓
A条件 and (B条件 or C条件)

A条件が共通なのにブロック毎に処理される。つまり、A条件が2回評価されるのでコストがかかる。
共通部分を1つにまとめるのがよい。


timestamp型の検索

timestamp型を日付で検索するとき、変換をかけるとその分遅くなるし、INDEXも使われない。
betweenを使うと速い。INDEXも使われるらしい!

select * from hoge where to_char(dt,'YYYYMMDD') = '20030713'
				↓
select * from hoge where dt between '2003/07/13 00:00:00' and '2003/07/13 23:59:59'

厳密には timestamp型はミリ秒以下まで値を持っているので 23:59:59.000より後の時刻が検索から洩れてしまうが、日付のみの(時刻を無視していい)検索だったら大丈夫。


maxとlimitは?

select max(no) from tbl_xxx where key = 'hoge';
select     no  from tbl_xxx where key = 'hoge'
order by no desc
limit 1;

どちらも同じ結果をもたらすが、後者の方がわずかにコストが大きい。


PostgreSQLへ戻る / 技術メモへ戻る
メールの送信先:ひしだま