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 template0


test_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 TABLE
test_collcate=# insert into t3 select m,'username'||m from generate_series(1,1000000) m;
INSERT 0 1000000
test_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 ms
postgres=# 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 INDEX
Time: 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
 
 
 --注意对于等于操作，如果字段上有索引，其是可以使用索引的