[pgsql-jp: 36447] Re: 複合インデックス時のORDER BY DESC （まとめ）

[池上 俊介]

池上です。

浅見さん 石井さん。ありがとうございます。
オペレータを使ったインデックスでorder by i,j DESC時に
sortが発生しないことを確認いたしました。

重複になりますが、アーカイブ用に最終的なSQLとexplain結果をまとめます。
（PostgreSQL7.4.7と8.0.3で確認）

----
CREATE OR REPLACE FUNCTION btint4cmprev(INTEGER, INTEGER) RETURNS
INTEGER AS '
	SELECT btint4cmp($2,$1);
' LANGUAGE sql;

CREATE OPERATOR CLASS int4_desc_ops FOR TYPE int4 USING BTREE AS
 OPERATOR 1 >, OPERATOR 2 >=, OPERATOR 3 =, OPERATOR 4 <=, OPERATOR 5 <,
FUNCTION 1 btint4cmprev(int4,int4);

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

--データを作成
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;


test=# EXPLAIN ANALYZE SELECT * FROM articles WHERE i = 100 ORDER BY j DESC;
                                                                QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1768.39..1771.01 rows=1046 width=13) (actual
time=53.960..58.169 rows=1001 loops=1)
   Sort Key: j
   ->  Index Scan using idx_articles_i_j on articles
(cost=0.00..1715.93 rows=1046 width=13) (actual time=0.065..4.975
rows=1001 loops=1)
         Index Cond: (i = 100)
 Total runtime: 62.472 ms
(5 rows)

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

-------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_articles_i_j on articles  (cost=0.00..1715.93
rows=1046 width=13) (actual time=0.048..4.840 rows=1001 loops=1)
   Index Cond: (i = 100)
 Total runtime: 9.088 ms
(3 rows)

test=# EXPLAIN ANALYZE SELECT * FROM articles WHERE i = 100 ORDER BY i,j;
                                                                QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1768.39..1771.01 rows=1046 width=13) (actual
time=9.642..13.839 rows=1001 loops=1)
   Sort Key: i, j
   ->  Index Scan using idx_articles_i_j on articles
(cost=0.00..1715.93 rows=1046 width=13) (actual time=0.028..4.831
rows=1001 loops=1)
         Index Cond: (i = 100)
 Total runtime: 18.152 ms
(5 rows)