POSTGRESQL区域设置对索引和排序的影响

--区域支持是在使用 initdb 创建一个数据库集群的时候自动初始化的，但可在创建数据库时单独指定--区域设置特别影响下面的 SQL 特性* 查询中使用 ORDER BY 或者对文本数据的标准比较操作符进行排序* upper, lower 和 initcap 函数* 模式匹配运算符(LIKE, SIMILAR TO, 以及 POSIX-风格的正则表达式)； 区域影响大小写不敏感的匹配和通过字符分类正则表达式的字符分类。* to_char 函数族* 使用 LIKE 子句的索引能力--其中有两个参数可以参考LC_COLLATE 字符串排序顺序LC_CTYPE 字符分类(什么是字母?是否区分大小写?)--LC_COLLATE 和 LC_CTYPE 的设置都是在数据库创建时决定的， 不能被改变除非创建新的数据库PostgreSQL 里使用非 C 或者 POSIX 区域的缺点是性能影响--查询服务器支持的区域select * from pg_collation;--查看当前数据库的区域设置show lc_ctype;show lc_collate;--下面针对不同的区域查看对索引的影响--创建数据库，指定区域createdb test_collcate --lc-collate=C --lc-ctype=C -T template0test_collcate=# show lc_ctype;show lc_collate; lc_ctype ---------- C(1 row)test_collcate=# show lc_collate; lc_collate ------------ C(1 row)--创建测试数据test_collcate=# create table t3 (id int ,user_name varchar(50));CREATE TABLEtest_collcate=# insert into t3 select m,'username'||m from generate_series(1,1000000) m;INSERT 0 1000000test_collcate=# create index idx_t3_user_name on t3(user_name);   CREATE INDEX--可以发现其使用了索引test_collcate=# explain analyze select count(*) from t3 where user_name like 'username123%';                                                              QUERY PLAN                                                               --------------------------------------------------------------------------------------------------------------------------------------- Aggregate  (cost=192.92..192.93 rows=1 width=0) (actual time=1.458..1.458 rows=1 loops=1)   ->  Index Only Scan using idx_t3_user_name on t3  (cost=0.42..192.67 rows=100 width=0) (actual time=0.157..1.313 rows=1111 loops=1)         Index Cond: ((user_name >= 'username123'::text) AND (user_name < 'username124'::text))         Filter: ((user_name)::text ~~ 'username123%'::text)         Heap Fetches: 1111 Total runtime: 28.043 ms     --在另外一个数据库的区域设置如下 postgres=# show lc_ctype; lc_ctype ---------- zh_CN(1 row)Time: 0.250 mspostgres=# show lc_collate; lc_collate ------------ zh_CN  --测试数据如下 postgres=# \d+ t2;                            Table "public.t2"  Column   |  Type   | Modifiers | Storage  | Stats target | Description -----------+---------+-----------+----------+--------------+------------- id        | integer |           | plain    |              |  user_name | text    |           | extended |              | Indexes:    "idx_t2_user_name" btree (user_name)Has OIDs: no--可以看到其不能使用索引postgres=# explain analyze select count(*) from t2 where user_name like 'rudy1111111111111%';                                                QUERY PLAN                                               -------------------------------------------------------------------------------------------------------- Aggregate  (cost=3583.31..3583.32 rows=1 width=0) (actual time=42.738..42.739 rows=1 loops=1)   ->  Seq Scan on t2  (cost=0.00..3583.26 rows=20 width=0) (actual time=42.734..42.734 rows=0 loops=1)         Filter: (user_name ~~ 'rudy1111111111111%'::text)         Rows Removed by Filter: 200101 Total runtime: 42.805 ms(5 rows)--如果数据库不使用标准的"C"区域设置，对于期望使LIKE 或者 POSIX 正则表达式，使用索引，--需要指定text_pattern_ops,varchar_pattern_ops 和 bpchar_pattern_ops 操作符类分别支持在 text, varchar 和 char 类型上的 B-tree 索引。 postgres=# create index idx_t3_user_name on t2(user_name varchar_pattern_ops);   CREATE INDEXTime: 450.281 ms--可以使用索引postgres=# explain analyze select count(*) from t2 where user_name like 'rudy1111111111111%';                                                           QUERY PLAN                                                            --------------------------------------------------------------------------------------------------------------------------------- Aggregate  (cost=8.49..8.50 rows=1 width=0) (actual time=0.183..0.184 rows=1 loops=1)   ->  Index Only Scan using idx_t3_user_name on t2  (cost=0.42..8.44 rows=20 width=0) (actual time=0.179..0.179 rows=0 loops=1)         Index Cond: ((user_name ~>=~ 'rudy1111111111111'::text) AND (user_name ~<~ 'rudy1111111111112'::text))         Filter: (user_name ~~ 'rudy1111111111111%'::text)         Heap Fetches: 0 Total runtime: 0.275 ms   --注意对于等于操作，如果字段上有索引，其是可以使用索引的