[pgsql-jp: 36439] 複合インデックス時のORDER BY DESC

[池上 俊介]

池上です。はじめまして。

メール末のarticlesテーブルに複合インデックスを作成し、
SELECT * FROM articles WHERE i = 30 ORDER BY j DESC LIMIT 10;
のような検索を高速化したいと思っています。

・PostgreSQLチューニングテクニック集
http://www2b.biglobe.ne.jp/~caco/fourth_edition/tuning.html
・MySQL-複合インデックスのすすめ
http://dev.seesaa.net/article/238633.html

上記を参考に
SELECT * FROM articles WHERE i = 30 ORDER BY j DESC LIMIT 10;
SELECT * FROM articles WHERE i = 30 ORDER BY i,j DESC LIMIT 10;
と行いましたが、sortが発生してデータ量が大きくなるほど遅くなります。
なおDESCをつけない以下は高速です。
SELECT * FROM articles WHERE i = 30 ORDER BY i,j LIMIT 10;

DESCをつけた状態でもsortを発生させずに
高速化する方法ありませんでしょうか？

SQLは以下で、PostgreSQL8.0.3をFedoraCore4上でテストしました。
よろしくお願いいたします。

----
事前にテーブルにcreatelang plpgsqlを設定しておく。

CREATE TABLE articles (
	i int,
	j int,
	name varchar(30)
);
CREATE INDEX idx_articles_i_j ON articles(i,j);

--データを作成
CREATE OR REPLACE FUNCTION insertdata() RETURNS int AS '
DECLARE
	i int := 0;
	j int := 0;
BEGIN
	FOR i IN 0..1000 LOOP
	  FOR j IN 0..1000 LOOP
		INSERT INTO articles VALUES (i,j,''a'');
	  END LOOP;
	END LOOP;
        RETURN 1;
END;
' LANGUAGE plpgsql;
SELECT insertdata();
VACUUM ANALYZE;
EXPLAIN ANALYZE SELECT * FROM articles WHERE i = 100 ORDER BY j DESC;
EXPLAIN ANALYZE SELECT * FROM articles WHERE i = 100 ORDER BY i,j DESC;
EXPLAIN ANALYZE SELECT * FROM articles WHERE i = 100 ORDER BY i,j;


---
explain結果

test=> EXPLAIN ANALYZE SELECT * FROM articles WHERE i = 100 ORDER BY j DESC;
                                                                QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2141.76..2144.26 rows=999 width=13) (actual
time=4.391..5.067 rows=1001 loops=1)
   Sort Key: j
   ->  Index Scan using idx_articles_i_j on articles
(cost=0.00..2091.99 rows=999 width=13) (actual time=0.314..2.742
rows=1001 loops=1)
         Index Cond: (i = 100)
 Total runtime: 6.140 ms
(5 rows)

test=> EXPLAIN ANALYZE SELECT * FROM articles WHERE i = 100 ORDER BY i,j
DESC;
                                                                QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2141.76..2144.26 rows=999 width=13) (actual
time=3.717..4.357 rows=1001 loops=1)
   Sort Key: i, j
   ->  Index Scan using idx_articles_i_j on articles
(cost=0.00..2091.99 rows=999 width=13) (actual time=0.036..1.918
rows=1001 loops=1)
         Index Cond: (i = 100)
 Total runtime: 5.312 ms
(5 rows)

test=> EXPLAIN ANALYZE SELECT * FROM articles WHERE i = 100 ORDER BY i,j;
                                                             QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_articles_i_j on articles  (cost=0.00..2091.99
rows=999 width=13) (actual time=0.038..1.935 rows=1001 loops=1)
   Index Cond: (i = 100)
 Total runtime: 2.663 ms
(3 rows)



----
池上俊介 shun ＠ baytime.com
横浜BaySide通信 http://baytime.cocolog-nifty.com