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

[藤澤]

こうもとさんへ

比較データは、こうもとさんが最初から書かれているように、
1,2,3 の内、1,3だけを考えれば良いんですね。
発想の転換になればと思い、出します。
速くなるかというと、このままではならないでしょうね。。。(T-T)



案：

格納するデータを 1,2,3 ではなく -1,0,1 とする。
比較データは、やっぱり変換して、1⇒-1, 2は無視、3は-1とする。
(便宜上、変換後は比較データＡと呼びますね。)

格納されているデータ{-1,0,1}と、対応する比較データＡを加算して、
data1〜data6 をかけ合わせて、結果が0でなければOK。
------------------------------------------------------------------------------------
WHERE
  (data1 + 比較データＡ_1) * (data2 + 比較データＡ_2)  ・・・  * (data6 + 比較データＡ_6) <> 0
------------------------------------------------------------------------------------


比較データの1〜6で、値が 2 のものがあれば、そこのとこが抜けます。
たとえば、比較データ1=2 かつ 比較データ3=2 であれば、以下のように。
------------------------------------------------------------------------------------
WHERE
  (data1 + 比較データＡ_2) * (data2 + 比較データＡ_4) * (data2 + 比較データＡ_5) * (data6 + 比較データＡ_6) <> 0
------------------------------------------------------------------------------------



発想の転換の一助となれば。
（あまりにもりあがっているので、参加したいという気持ちもあります。(^^;)）


／藤澤



On Thu, 20 Sep 2007 10:42:13 +0900 (JST)
河本陽一 <komoto.yoichi ＠ kcc.co.jp> wrote:

> こうもとです。
> 
> 　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