PostgreSQLでEUCJPとUTF8のTEXT中間一致検索速度を比較

txt = File.read("元テキストファイル")
# 色々消す
# tsvとして読み込ませるため \t   念の為 \r
# SQLの挿入で困らないように \\   空白行 \n{2,}
txt.delete!("\t\r\\")
txt.gsub!(/\n{2,}/, "\n")

opts =  {:undef=>:replace, :invalid=>:replace, :replace=>""}
utf8_txt = txt.encode(Encoding::EUC_JP, opts).encode(Encoding::UTF_8, opts)
eucjp_txtx = utf8_txt.encode(Encoding::EUC_JP, opts)

$ sudo  localedef -f EUC-JP -i ja_JP ja_JP.EUC-JP

postgres=# CREATE DATABASE utf8 WITH ENCODING 'UTF8';
CREATE DATABASE
postgres=# CREATE DATABASE "eucjp" WITH TEMPLATE="template0" ENCODING='EUC_JP' LC_COLLATE='C' LC_CTYPE='C';
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 eucjp     | postgres | EUC_JP   | C           | C           | 
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 utf8      | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | 
(5 rows)

postgres=# \c utf8
You are now connected to database "utf8" as user "postgres".
utf8=# CREATE TABLE utf8text ( text text );
CREATE TABLE
eucjp=# \encoding
UTF8
utf8=# COPY utf8text FROM '/path/to/utf8.txt';
COPY 10191461
utf8=# \c eucjp
You are now connected to database "eucjp" as user "postgres".
eucjp=# CREATE TABLE eucjptext ( text text );
CREATE TABLE
eucjp=# \encoding EUC-JP
eucjp=# \encoding
EUC_JP
eucjp=# COPY eucjptext FROM '/path/to/eucjp.txt';
COPY 10191461

-- なんとなくデータベースの容量を確認
eucjp=# SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;
  datname  | pg_size_pretty 
-----------+----------------
 postgres  | 7629 kB
 template1 | 7497 kB
 template0 | 7497 kB
 utf8      | 2961 MB
 eucjp     | 2142 MB
(5 rows)

postgres=# \timing
Timing is on.
postgres=# \c utf8 
You are now connected to database "utf8" as user "postgres".
--　下記コマンドをそれぞれ5回
utf8=# SELECT count(text) FROM utf8text WHERE text LIKE '%に%';
utf8=# SELECT count(text) FROM utf8text WHERE text LIKE '%ナレッジコミュニティ%';

utf8=# \c eucjp 
You are now connected to database "eucjp" as user "postgres".
--　下記コマンドをそれぞれ5回
eucjp=# SELECT count(text) FROM eucjptext WHERE text LIKE '%に%';
eucjp=# SELECT count(text) FROM eucjptext WHERE text LIKE '%ナレッジコミュニティ%';