PostgreSQLにおけるロケールの影響調査

CREATE DATABASE locale_c
  TEMPLATE template0 ENCODING 'UTF-8'
  LC_COLLATE 'C' LC_CTYPE 'C';

CREATE DATABASE locale_jp_utf8
  TEMPLATE template0 ENCODING 'UTF-8'
  LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8';

CREATE DATABASE locale_us_utf8
  TEMPLATE template0 ENCODING 'UTF-8'
  LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';

-- ロケールCのデータベースに接続して、UTF-8の全文字を格納するテーブルall_utf8を作成
\c locale_c
CREATE EXTENSION pg_cheat_funcs;
CREATE TABLE all_utf8 AS SELECT * FROM pg_all_utf8();
VACUUM FREEZE ANALYZE all_utf8;

-- ロケールja_JP.UTF-8のデータベースに接続して、UTF-8の全文字を格納するテーブルall_utf8を作成
\c locale_jp_utf8
CREATE EXTENSION pg_cheat_funcs;
CREATE TABLE all_utf8 AS SELECT * FROM pg_all_utf8();
VACUUM FREEZE ANALYZE all_utf8;

-- ロケールen_US.UTF-8のデータベースに接続して、UTF-8の全文字を格納するテーブルall_utf8を作成
\c locale_us_utf8
CREATE EXTENSION pg_cheat_funcs;
CREATE TABLE all_utf8 AS SELECT * FROM pg_all_utf8();
VACUUM FREEZE ANALYZE all_utf8;

-- SQL実行時間を計測するため、psqlの\timingオプションを有効化
\timing on

-- ロケールCのデータベースで、UTF-8の全文字のソートを実行
\c locale_c
\o /tmp/locale_c_orderby.txt
SELECT code, utf8 FROM all_utf8 ORDER BY utf8;

-- ロケールja_JP.UTF-8のデータベースで、UTF-8の全文字のソートを実行
\c locale_jp_utf8
\o /tmp/locale_jp_utf8_orderby.txt
SELECT code, utf8 FROM all_utf8 ORDER BY utf8;

-- ロケールen_US.UTF-8のデータベースで、UTF-8の全文字のソートを実行
\c locale_us_utf8
\o /tmp/locale_us_utf8_orderby.txt
SELECT code, utf8 FROM all_utf8 ORDER BY utf8;

-- SQL実行結果のファイル出力を無効化
\o

-- ソート結果をdiffで比較
\! diff /tmp/locale_c_orderby.txt /tmp/locale_jp_utf8_orderby.txt
\! diff /tmp/locale_c_orderby.txt /tmp/locale_us_utf8_orderby.txt

-- SQL実行時間を計測するため、psqlの\timingオプションを有効化
\timing on

-- ロケールCのデータベースで、UTF-8の全文字のB-treeインデックスを作成
\c locale_c
CREATE INDEX utf8_Idx ON all_utf8(utf8);

-- ロケールja_JP.UTF-8のデータベースで、UTF-8の全文字のB-treeインデックスを作成
\c locale_jp_utf8
CREATE INDEX utf8_Idx ON all_utf8(utf8);

-- ロケールen_US.UTF-8のデータベースで、UTF-8の全文字のB-treeインデックスを作成
\c locale_us_utf8
CREATE INDEX utf8_Idx ON all_utf8(utf8);

-- SQL実行時間を計測するため、psqlの\timingオプションを有効化
\timing on

-- ロケールCのデータベースで、UTF-8の全文字の完全一致/範囲/前方一致検索を実行
\c locale_c
EXPLAIN ANALYZE SELECT code, utf8 FROM all_utf8 WHERE utf8 IN ('a', 'ポ', 'Ⅰ');
EXPLAIN ANALYZE SELECT count(*) FROM all_utf8 WHERE utf8 BETWEEN 'ア' AND 'ン';
EXPLAIN ANALYZE SELECT code, utf8 FROM all_utf8 WHERE utf8 LIKE 'グ%';

-- ロケールja_JP.UTF-8のデータベースで、UTF-8の全文字の完全一致/範囲/前方一致検索を実行
\c locale_jp_utf8
EXPLAIN ANALYZE SELECT code, utf8 FROM all_utf8 WHERE utf8 IN ('a', 'ポ', 'Ⅰ');
EXPLAIN ANALYZE SELECT count(*) FROM all_utf8 WHERE utf8 BETWEEN 'ア' AND 'ン';
EXPLAIN ANALYZE SELECT code, utf8 FROM all_utf8 WHERE utf8 LIKE 'グ%';

-- ロケールen_US.UTF-8のデータベースで、UTF-8の全文字の完全一致/範囲/前方一致検索を実行
\c locale_us_utf8
EXPLAIN ANALYZE SELECT code, utf8 FROM all_utf8 WHERE utf8 IN ('a', 'ポ', 'Ⅰ');
EXPLAIN ANALYZE SELECT count(*) FROM all_utf8 WHERE utf8 BETWEEN 'ア' AND 'ン';
EXPLAIN ANALYZE SELECT code, utf8 FROM all_utf8 WHERE utf8 LIKE 'グ%';

-- SQL実行時間を計測するため、psqlの\timingオプションを有効化
\timing on

-- ロケールCのデータベースで、UTF-8の全文字のlowerとupperを実行
\c locale_c
\o /tmp/locale_c_lower.txt
SELECT code, utf8, lower(utf8) FROM all_utf8 WHERE utf8 <> lower(utf8) ORDER BY code;
\o /tmp/locale_c_upper.txt
SELECT code, utf8, upper(utf8) FROM all_utf8 WHERE utf8 <> upper(utf8) ORDER BY code;

-- ロケールja_JP.UTF-8のデータベースで、UTF-8の全文字のlowerとupperを実行
\c locale_jp_utf8
\o /tmp/locale_jp_utf8_lower.txt
SELECT code, utf8, lower(utf8) FROM all_utf8 WHERE utf8 <> lower(utf8) ORDER BY code;
\o /tmp/locale_jp_utf8_upper.txt
SELECT code, utf8, upper(utf8) FROM all_utf8 WHERE utf8 <> upper(utf8) ORDER BY code;

-- ロケールen_US.UTF-8のデータベースで、UTF-8の全文字のlowerとupperを実行
\c locale_us_utf8
\o /tmp/locale_us_utf8_lower.txt
SELECT code, utf8, lower(utf8) FROM all_utf8 WHERE utf8 <> lower(utf8) ORDER BY code;
\o /tmp/locale_us_utf8_upper.txt
SELECT code, utf8, upper(utf8) FROM all_utf8 WHERE utf8 <> upper(utf8) ORDER BY code;

-- SQL実行結果のファイル出力を無効化
\o

-- lower結果をdiffで比較
\! diff /tmp/locale_c_lower.txt /tmp/locale_jp_utf8_lower.txt
\! diff /tmp/locale_c_lower.txt /tmp/locale_us_utf8_lower.txt
\! diff /tmp/locale_jp_utf8_lower.txt /tmp/locale_us_utf8_lower.txt

-- upper結果をdiffで比較
\! diff /tmp/locale_c_upper.txt /tmp/locale_jp_utf8_upper.txt
\! diff /tmp/locale_c_upper.txt /tmp/locale_us_utf8_upper.txt
\! diff /tmp/locale_jp_utf8_upper.txt /tmp/locale_us_utf8_upper.txt