[pgsql-jp: 29030] Re: Like を使った前方一致検索時のインデックス使用条件について

[ISHIDA Akio]

こんにちは。石田@苫小牧市です。

ちょっと単純な実験してみました。ruby で、

a ='AA'
(1..10000).each {
  print "#{a[-2..-1]}ZZZZ\n"
  a.succ!
}

と 10000 件のテストデータを作ります。頭 2 ケタが AA,AB,AC となり
後 4 ケタが固定、こんなカンジです。

AAZZZZ
ABZZZZ
ACZZZZ
..
ZXZZZZ
ZYZZZZ
ZZZZZZ
AAZZZZ
ABZZZZ
..

で、

ishida=> CREATE TABLE t1(f text);
CREATE TABLE
ishida=> \copy t1 from 'testdata'
\.
ishida=> CREATE INDEX t_idx ON t1(f);
CREATE INDEX
ishida=> VACUUM ANALYZE t1;
VACUUM
ishida=> EXPLAIN SELECT * from t1 WHERE f LIKE 'A%';
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on t1  (cost=0.00..180.00 rows=436 width=10)
   Filter: (f ~~ 'A%'::text)
(2 rows)

ishida=> EXPLAIN SELECT * from t1 WHERE f LIKE 'AA%';
                           QUERY PLAN
-----------------------------------------------------------------
 Index Scan using t_idx on t1  (cost=0.00..5.96 rows=1 width=10)
   Index Cond: ((f >= 'AA'::text) AND (f < 'AB'::text))
   Filter: (f ~~ 'AA%'::text)
(3 rows)

ishida=> EXPLAIN SELECT * from t1 WHERE f LIKE 'BB%';
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using t_idx on t1  (cost=0.00..54.05 rows=15 width=10)
   Index Cond: ((f >= 'BB'::text) AND (f < 'BC'::text))
   Filter: (f ~~ 'BB%'::text)
(3 rows)

'A%' で Seq Scan で 'AA%' で Index Scan になりました。
'AA%' だと rows=1 で 'BB%' だと rows=15 になるのはちょっと変ですが。

もちろん、

ishida=> select count(*) from t1 where f like 'AA%';
 count
-------
    15
(1 row)

ishida=> select count(*) from t1 where f like 'BB%';
 count
-------
    15
(1 row)

と、本当の数はいっしょなのですが。

まあ、こんなに均一なデータではないとは思うのですが。
データの分布によっては、部分インデックスを使うとか、
先頭2文字で検索するときまっているのであれば、関数インデックスを
つかうとか方法があるかもしれません。

On Thu, Feb 13, 2003 at 05:28:33PM +0900, Hajime Lucky Okada wrote:
> こんにちは、岡田と申します。 お世話になっております。
> 
> 表題の件に関し、非常にシンプルなテープル構成に関わらず
> 使用／設定方法が悪いのか、人間の期待した動きをしてくれません。
> 
> それは、ある単一カラムの % を使用した前方一致の検索です。
> 
> 
> 次に示す検索対象カラム mitumori は、text 型で実質 全て 6 桁の英文字、
> 高々 1 万件程度です。
> このカラムには、通常の方法でインデックスを作成しています。
> create index idx1_plan on kenmei(mitumori);
> 
> このカラムに対し、次の様に 'SI' から始まるレコードを検索してみました。
> select plan_id, kenmei from kenmei where mitumori LIKE 'SI%';
> マシン自体が貧弱なせいもあり、返事の返ってくるまで うっと考えているのが分かり、
> もしかしてということで explain を掛けてみました。
> 
> xxxdb=> explain select plan_id, kenmei from kenmei where mitumori LIKE 'SI%';
>                            QUERY PLAN
> ----------------------------------------------------------------
>  Seq Scan on kenmei  (cost=0.00..421.15 rows=1402 width=38)
>    Filter: (mitumori ~~ 'SI%'::text)
> (2 rows)
> 
> インデックス検索を期待していたのですが、結果は sequential scan でした。
> 正規表現を使用しても変わりません。
>

-- 
ISHIDA Akio