[pgsql-jp: 28803] 集計処理を快適にしたい

[桝形 誠二]

こんにちわ、桝形です。

10個の色の中から、どの色が一番いいのかを投票してもらって
その結果を集計し、上位５位までを表示するクエリを作成して
いるのですが、快適にできず、逆にえらい事になっています。

【PostgreSQL】

7.3.1( SunOS5.6 )

【テーブル構造】

CREATE TABLE test4
(
 no1 INT          NOT NULL,
 no2 VARCHAR(100) NOT NULL
);

CREATE INDEX test4_no1_index_key ON test4( no1 );
CREATE INDEX test4_no2_index_key ON test4( no2 );

【データ】

INSERT INTO test4 VALUES( 1, '赤' );
INSERT INTO test4 VALUES( 1, '赤' );
INSERT INTO test4 VALUES( 1, '赤' );
INSERT INTO test4 VALUES( 1, '赤' );
INSERT INTO test4 VALUES( 2, '赤' );
INSERT INTO test4 VALUES( 2, '赤' );
INSERT INTO test4 VALUES( 3, '青' );
INSERT INTO test4 VALUES( 3, '青' );
INSERT INTO test4 VALUES( 3, '青' );
INSERT INTO test4 VALUES( 3, '青' );
INSERT INTO test4 VALUES( 4, '黒' );
INSERT INTO test4 VALUES( 4, '黒' );
                          ↑  ↑
             投票した人のID   投票した色

以下、延々と100万件まで続く。

上記の環境でテーブル構造及びデータになっています。

ただ、テーブル構造等に関してはこだわっている訳ではなく
もっとよい構造であればそれでも構いません。

問題のクエリですが、

select * from (
               select no2, count( * ) as cnt from test4
               group by no2 offset 0 limit 5
              ) as test
order by cnt desc;

データ量が100万件あるので、上記のSQLでは結果的に全件
見にいって遅くなっているという事なんでしょうか？
( 最後に上位５件を抽出しているので )

EXPLAINの結果は下記になります。

 Sort  (cost=1.31..1.33 rows=5 width=6)
   Sort Key: cnt
   ->  Subquery Scan test  (cost=0.00..1.26 rows=5 width=6)
         ->  Limit  (cost=0.00..1.26 rows=5 width=6)
               ->  Aggregate  (cost=0.00..25115.00 rows=100000 width=6)
                     ->  Group  (cost=0.00..22615.00 rows=1000000 width=6)
                           ->  Index Scan using test4_no2_idndex_key on test4  (cost=0.00..20115.00 rows=1000000 width=6)

もっとよいやり方を知っておられる方は、大変お手数ですが
ご教授願います。よろしくお願いします。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿

   桝形　誠二( Masugata Seiji )
   E-Mail ： s.masugata ＠ digicom.dnp.co.jp

￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣