[pgsql-jp: 38773] 3種類の値を持つ6つの項目の検索方法

[河本陽一]

こうもとです。

　PostgreSQL 8.1.9を使用しています。
　3種類の値を持つ6つの項目の中から、条件にあうデータを検索しようとし
ています。なかなか思うような速度が出ないので、何かよい案がないかと思っ
ています。

　3種類の値が、1,2,3とする場合、
・比較データが1のときは1と2
・比較データが2のときは1と2と3(全部)
・比較データが3のときは2と3
　のデータを全て見つけようとしています。

　データの頻度はほとんどが1か3で、200件に1回程度の割合で2があります。
　条件に合う件数は、全体の1.5%程度です。

　配列だとシーケンシャルスキャンしか行ってくれなかったので、6つのフィー
ルドを作るとビットマップスキャンなどを使って検索時間が半分程度になり
ました。
　さらに高速にしようとビット列を使ってみたのですが、インデックスが
使用されずに速度は出ませんでした。1を01、2を11、3を10とし、各桁をdatah
とdatalに保存しています。

　データ投入後は「VACUUM ANALYZE」を実行しています。

　値や頻度は変わりませんが、項目は今後数倍に増える可能性があります。
　検索時の速度を最優先としたいのですが、データが増えてもテーブルの変
更を減らしたり、さらに早い検索方法がないか探しています。
　値の持ち方は何でも良く、3種類の区別さえできれば問題ありません。


●テーブルイメージ
CREATE TABLE img (
    imgno   serial PRIMARY KEY,
    datah   varbit,
    datal   varbit,
    data1   int,
    data2   int,
    data3   int,
    data4   int,
    data5   int,
    data6   int,
    data    int[]
);
GRANT SELECT,INSERT,UPDATE,DELETE ON img TO youichi;
GRANT UPDATE ON img_imgno_seq TO youichi;
CREATE INDEX img_data_idx ON img(data1,data2,data3,data4,data5,data6);
CREATE INDEX img_datahl_idx ON img(datah,datal);
CREATE INDEX img_datal_idx ON img(datal);

●検索
img=# set enable_seqscan TO no;
SET
img=# EXPLAIN ANALYZE SELECT imgno,data FROM img WHERE data1 >= 2 AND data2 >= 2
 AND data3 >= 2 AND data4 <= 2 AND data5 <= 2 AND data6 <= 2;
                                                           QUERY PLAN           
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on img  (cost=1170.40..2918.44 rows=1716 width=52) (actual time=95.205..614.003 rows=1598 loops=1)
   Recheck Cond: ((data1 >= 2) AND (data2 >= 2) AND (data3 >= 2) AND (data4 <= 2) AND (data5 <= 2) AND (data6 <= 2))
   ->  Bitmap Index Scan on img_data_idx  (cost=0.00..1170.40 rows=1716 width=0) (actual time=94.513..94.513 rows=1598 loops=1)
         Index Cond: ((data1 >= 2) AND (data2 >= 2) AND (data3 >= 2) AND (data4 <= 2) AND (data5 <= 2) AND (data6 <= 2))
 Total runtime: 673.310 ms
(5 rows)

img=# EXPLAIN ANALYZE SELECT imgno,data FROM img WHERE ((datah & B'111000') | (datal & B'000111')) = B'111111';
                                                     QUERY PLAN                 
---------------------------------------------------------------------------------------------------------------------
 Seq Scan on img  (cost=100000000.00..100003640.00 rows=500 width=52) (actual time=7.696..697.203 rows=1598 loops=1)
   Filter: ((((datah)::"bit" & B'111000'::"bit") | ((datal)::"bit" & B'000111'::"bit")) = B'111111'::"bit")
 Total runtime: 759.773 ms
(3 rows)


======================================================================
河本陽一(こうもとよういち)
mailto:komoto.yoichi ＠ kcc.co.jp