[pgsql-jp: 38774] Re: 3種類の値を持つ6つの項目の検索方法
藤澤
qsecofr1 ＠ hotmail.com
2007年 9月 20日 (木) 13:47:39 JST
こうもとさんへ
藤澤です。
取り出したいレコードの条件が良く分かりません。
テーブルのイメージは掴めたのですが、、、。
------------------------------------------------------
> 3種類の値が、1,2,3とする場合、
> ・比較データが1のときは1と2
> ・比較データが2のときは1と2と3(全部)
> ・比較データが3のときは2と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[]
> );
------------------------------------------------------
比較データというのは、所与の条件として、外部から与えられる値
ですね？
で、この比較データの値に応じて、条件が３パターン挙げておられる
ことまでは分かります。(分かったつもりです。)
で、ここまでの理解で書くと、(すみません。Oracle の PL/SQLです。)
以下になります。
----------------------------------------------
declare
n_hikakuData number(1) := 1;
n_cnt number;
begin
SELECT COUNT(*) INTO n_cnt
FROM img
WHERE
(n_hikakuData = 1 AND data1 IN (1, 2)) OR
(n_hikakuData = 2 ) OR
(n_hikakuData = 3 AND data1 IN (2, 3));
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n_cnt) || ' 件');
end;
----------------------------------------------
でも、ちょっとずれてますよね、これ？
条件を、別の言い方で教えてもらえませんか？
／藤澤
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
pgsql-jp メーリングリストの案内