CentOS 4.9 &PostgreSQL (postgresql-server-8.1.11-1.el4s1.1)での話。
- 200以上のカラムを持つテーブルが数10あるDBを構築
- 各テーブルにはName varchar(10)とTime datatimeのカラムを含む
- Nameに対してbtreeのインデックスが張ってある
- 同じNameを持つ行が1~数行存在し、 NameとTimeの組み合わせでは重複は無い
こういうテーブルに対し以下のSQLを発行すると2~3秒かかる現象が。
SELECT max(Time),min(Time) FROM table1 WHERE Name='hoge'
つまりName='hoge'がどの時間範囲で存在しているかを知るのが目的。似たような構造を持つテーブルは他にもたくさんあるのに、現象が発生するテーブルは一つだけ。しかもデータ行が100万を越えた辺りから発生する。テーブルを一旦削除してデータを入れなおしても同じ。
一方以下のクエリは見た目一瞬で完了。
SELECT Name FROM table1 WHERE Name='hoge' SELECT count(Name) FROM table1 WHERE Name='hoge'
最終的に以下のクエリが期待している速度で完了するようなので、理由はともかく採用。
SELECT max(Time),min(Time) FROM table1 WHERE Name='hoge' GROUP BY Name
Name自体はSELECTする項目ではなくWHERE句でもNameは単一として指定しているので、GROUP BYの有無に関わらず出る結果は同じはず。一方オプティマイザは両者では異なる方針を出すようで、GROUP BYを付けないとインデックスを2回使ったりTimeがNULLかどうかを調べたり。
db1=> explain SELECT max(Time),min(Time) FROM table1 WHERE Name='hoge' GROUP BY Name; QUERY PLAN ---------------------------------------------------------------------------------------------- GroupAggregate (cost=0.00..2908.69 rows=1 width=22) -> Index Scan using index1 on table1 (cost=0.00..2901.15 rows=1003 width=22) Index Cond: (Name = 'hoge'::bpchar) (3 rows) db1=> explain SELECT max(Time),min(Time) FROM table1 WHERE Name='hoge'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Result (cost=526.88..526.89 rows=1 width=0) InitPlan -> Limit (cost=0.00..263.44 rows=1 width=8) -> Index Scan Backward index1 on table1 (cost=0.00..264491.27 rows=1004 width=8) Filter: ((Time IS NOT NULL) AND (Name = 'hoge'::bpchar)) -> Limit (cost=0.00..263.44 rows=1 width=8) -> Index Scan using index1 on table1 (cost=0.00..264491.27 rows=1004 width=8) Filter: ((Time IS NOT NULL) AND (Name = 'hoge'::bpchar)) (8 rows)
う~む、よく分からん(- -;)
Comments