[pgsql-jp: 36447] Re: 複合インデックス時のORDER BY DESC (まとめ)
池上 俊介
shun @ baytime.com
2005年 11月 28日 (月) 00:39:01 JST
池上です。
浅見さん 石井さん。ありがとうございます。
オペレータを使ったインデックスで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)
pgsql-jp メーリングリストの案内