[pgsql-jp: 29725] substring を使用した場合の検索結果について

[Hirokazu Matsumura]

皆様こんばんは。松村と申します。
今回、初めて投稿させて頂きます。

現在、MSDE(SqlServer)から、PostgreSQLへ移行を行っております。
しかし、Select文での検索が非常に遅くて困っております。
(同一SQLにて、SqlServerでは数秒のものがPostgreSQLでは数十秒かかる)

実際の業務では、
２万件程度のデータを主として、substringで切り出した項目で
１０万件程度のデータの項目にあてています。
(where substring(test01.col3,1,10) = test2.col1)

必要と思われる項目にindexを設定し、vacuumをかけたのですが
速度が改善されませんでした。
そこで、explain analyzeを使用してどの程度実行時間が
かかっているのか調べているのですが．．．

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
動作環境
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CPU         Pentium2 333MHz
memory      128MB
Linux version 2.4.18-3
PostgreSQL 7.3.1
PHP 4.3.1

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
テーブル情報（名称は適当です）
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Table "public.test01"
Column      Type                          Modifiers
col1  character varying(9)    not null
col2  character varying(6)
col3  character varying(13)
...
col10
Indexes: test01_pkey primary key btree (col1),
         col2 btree (col2),
         col3 btree (col3)

データ件数：20000件程度です。

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
実行結果１
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
explain analyze
select *
from test01
where col1 = '99XXX';
 Index Scan using test01_pkey on test01  (cost=0.00..3.34 rows=1
width=238)
 (actual time=18.88..18.88 rows=1 loops=1)
   Index Cond: (col1 = '99XXX'::character varying)
 Total runtime: 1.15 msec
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
実行結果２
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
explain analyze
select *
from test01
where substring(test01,1,9) = '99XXX';
Seq Scan on test01  (cost=0.00..1191.36 rows=108 width=238)
 (actual time=175.69..345.42 rows=1 loops=1)
   Filter: ("substring"((col1)::text, 1, 9) = '99XXX'::text)
 Total runtime: 345.73 msec
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
と、以上の様になっておりました。
実行速度が遅いのは、substring関数を使用しているからなのか？
とも思うのですが、検索方法が「Index -> Seq」になってしまっている
のが何故かわかりません。
（これもsubstring関数を使用している事と関係が有るのでしょうか？）

私としては、ここの部分が検索時間を遅くしている原因ではないか？
と思っているのですが．．．

これを解決する方法は有るのでしょうか？
又、調査をする上でのヒントとなるような事が有りましたら
教えて頂けないでしょうか？

----------------------------------------------
松村 浩一
----------------------------------------------