自分用メモ - メモ

全部自分用メモなんだけれど。

PostgreSQLのロケールの設定がイケてないとLIKEでの前方一致検索時にインデックスが効かない。

データベース名は「test001」。

データベース作成時にはロケールの指定を行わない。

インデックス作成時にtext_pattern_opsを付けてやることで前方一致の時もインデックスが使用される。

データベース作成時にロケールを「C」とかにしていれば上記オプション（？）は必要ない。

以下、ちょっとしたお試し。

test001=#

test001=# select version();

version

-------------------------------------------------------------

PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit

(1 行)

test001=#

test001=#

test001=#

test001=# select name, setting, context from pg_settings where name like 'lc%';

name | setting | context

-------------+-----------------+-----------

lc_collate | Japanese, Japan | internal

lc_ctype | Japanese, Japan | internal

lc_messages | Japanese, Japan | superuser

lc_monetary | Japanese, Japan | user

lc_numeric | Japanese, Japan | user

lc_time | Japanese, Japan | user

(6 行)

test001=#

test001=#

test001=#

test001=# create table locale_test1(name varchar(10));

CREATE TABLE

test001=# create table locale_test2(name varchar(10));

CREATE TABLE

test001=#

test001=#

test001=# create index idx_test1 on locale_test1(name);

CREATE INDEX

test001=#

test001=# create index idx_test2 on locale_test2(name text_pattern_ops);

CREATE INDEX

test001=#

test001=#

test001=# \d locale_test1

テーブル "public.locale_test1"

カラム | 型 | 修飾語

--------+-----------------------+--------

name | character varying(10) |

インデックス:

"idx_test1" btree (name)

test001=#

test001=#

test001=# \d locale_test2

テーブル "public.locale_test2"

カラム | 型 | 修飾語

--------+-----------------------+--------

name | character varying(10) |

インデックス:

"idx_test2" btree (name text_pattern_ops)

test001=#

test001=#

test001=# CREATE FUNCTION test1() RETURNS INTEGER AS

test001-# $$

test001$# DECLARE

test001$# CNT INTEGER;

test001$# BEGIN

test001$# FOR CNT IN 1..1000 LOOP

test001$# INSERT INTO locale_test1 VALUES (CNT);

test001$# END LOOP;

test001$# RETURN CNT;

test001$# END;

test001$# $$

test001-# LANGUAGE plpgsql;

CREATE FUNCTION

test001=#

test001=#

test001=#

test001=#

test001=# SELECT test1();

test1

-------

0

(1 行)

test001=#

test001=# select count(*) from locale_test1;

count

-------

1000

(1 行)

test001=# select * from locale_test1 limit 10;

name

------

1

2

3

4

5

6

7

8

9

10

(10 行)

test001=#

test001=#

test001=# explain analyze

test001-# select * from locale_test1 where name='1';

QUERY PLAN

---------------------------------------------------------------------------------------------------

--------------------

Index Scan using idx_test1 on locale_test1 (cost=0.00..8.27 rows=1 width=3) (actual time=0.043..0

048 rows=1 loops=1)

Index Cond: *1

Filter: ((name)::text ~~ '100%'::text)

Total runtime: 0.118 ms

(4 行)

test001=#

*1:name)::text = '1'::text)

Total runtime: 0.112 ms

(3 行)

test001=#

test001=# explain analyze

test001-# select * from locale_test1 where name like '100%';

QUERY PLAN

---------------------------------------------------------------------------------------------------

---

Seq Scan on locale_test1 (cost=0.00..17.50 rows=1 width=3) (actual time=0.081..0.545 rows=2 loops

Filter: ((name)::text ~~ '100%'::text)

Total runtime: 0.607 ms

(3 行)

test001=#

test001=# CREATE FUNCTION test2() RETURNS INTEGER AS

test001-# $$

test001$# DECLARE

test001$# CNT INTEGER;

test001$# BEGIN

test001$# FOR CNT IN 1..1000 LOOP

test001$# INSERT INTO locale_test2 VALUES (CNT);

test001$# END LOOP;

test001$# RETURN CNT;

test001$# END;

test001$# $$

test001-# LANGUAGE plpgsql;

CREATE FUNCTION

test001=#

test001=# SELECT test2();

test2

-------

(1 行)

test001=#

test001=# select count(*) from locale_test2;

count

-------

1000

(1 行)

test001=#

test001=# select * from locale_test2 limit 10;

name

------

(10 行)

test001=#

test001=# select * from locale_test2 where name='1';

name

------

(1 行)

test001=# explain analyze

test001-# select * from locale_test2 where name='1';

QUERY PLAN

---------------------------------------------------------------------------------------------------

--------------------

Index Scan using idx_test2 on locale_test2 (cost=0.00..8.27 rows=1 width=3) (actual time=0.031..0

035 rows=1 loops=1)

Index Cond: ((name)::text = '1'::text)

Total runtime: 0.095 ms

(3 行)

test001=#

test001=# explain analyze

test001-# select * from locale_test2 where name like '100%';

QUERY PLAN

---------------------------------------------------------------------------------------------------

--------------------

Index Scan using idx_test2 on locale_test2 (cost=0.00..8.27 rows=1 width=3) (actual time=0.040..0

052 rows=2 loops=1)

Index Cond: (((name)::text ~>=~ '100'::text) AND ((name)::text ~<~ '101'::text