Collate的设置会影响index的使用，比如虽然数据库的Collate为A，创建索引时指定Collate为A，在查询时也不会走该Index。 测试脚本 ``` drop database testdb; create database testdb lc_collate 'C'; \c testdb select datname,datcollate from pg_database where datname = current_database; \timing on drop table p1; create table p1( partid varchar(32) not null, col1 varchar(2) not null, col2 varchar(20) not null, col3 varchar(20) not null )partition by range(partid) ( partition part1 values less than ('20210229'), partition part2 values less than ('20210330'), partition part3 values less than ('20210430'), partition part4 values less than ('20210530') ); insert into p1 select '20210301','1',x,x from generate_series(1,1000000) x; -- insert into p1 select '20210301','1',x,x from generate_series(1,1000000) x; -- insert into p1 select '20210401','1',x,x from generate_series(1,1000000) x; insert into p1 select '20210501','1',x,x from generate_series(1,1000000) x; drop index idx_p1_col2; create index idx_p1_col2 on p1(col2 collate "C")local; drop index idx_p1_col3; create index idx_p1_col3 on p1(col3) local; analyze p1; explain analyze select * from p1 where col2='30034'; explain analyze select * from p1 where col3='30034'; select datname,datcollate from pg_database where datname = current_database; ``` 执行上述脚本 1、创建collate为C的数据库 ``` vb225=# drop database testdb; DROP DATABASE vb225=# create database testdb lc_collate 'C'; CREATE DATABASE vb225=# \c testdb NOTICE: Last successful login info login time:2022-04-30 10:02:27 application name:statctl Ip address:[local] method:Trust Failed 0 times since last successful login Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "testdb" as user "vb225". testdb=# select datname,datcollate from pg_database where datname = current_database; datname | datcollate ---------+------------ testdb | C (1 row) testdb=# ``` 2、创建表，并插入数据 ``` testdb=# drop table p1; ERROR: table "p1" does not exist testdb=# create table p1( testdb(# partid varchar(32) not null, testdb(# col1 varchar(2) not null, testdb(# col2 varchar(20) not null, testdb(# col3 varchar(20) not null testdb(# )partition by range(partid) testdb-# ( testdb(# partition part1 values less than ('20210229'), testdb(# partition part2 values less than ('20210330'), testdb(# partition part3 values less than ('20210430'), testdb(# partition part4 values less than ('20210530') testdb(# ); CREATE TABLE testdb=# testdb=# insert into p1 select '20210301','1',x,x from generate_series(1,1000000) x; INSERT 0 1000000 testdb=# -- insert into p1 select '20210301','1',x,x from generate_series(1,1000000) x; testdb=# -- insert into p1 select '20210401','1',x,x from generate_series(1,1000000) x; testdb=# insert into p1 select '20210501','1',x,x from generate_series(1,1000000) x; INSERT 0 1000000 testdb=# ``` 3、创建索引，其中一个索引制定collate为C ``` testdb=# drop index idx_p1_col2; ERROR: index "idx_p1_col2" does not exist testdb=# create index idx_p1_col2 on p1(col2 collate "C")local; CREATE INDEX testdb=# testdb=# drop index idx_p1_col3; ERROR: index "idx_p1_col3" does not exist testdb=# create index idx_p1_col3 on p1(col3) local; CREATE INDEX testdb=# testdb=# analyze p1; ANALYZE ``` 4、执行SQL ``` testdb=# explain analyze select * from p1 where col2='30034'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Partition Iterator (cost=0.00..39616.00 rows=2 width=23) (actual time=15.007..305.669 rows=2 loops=1) Iterations: 4 -> Partitioned Seq Scan on p1 (cost=0.00..39616.00 rows=2 width=23) (actual time=19.177..305.629 rows=2 loops=4) Filter: ((col2)::text = '30034'::text) Rows Removed by Filter: 1999998 Selected Partitions: 1..4 Total runtime: 305.730 ms, Peak Memory :176 (KB) (7 rows) testdb=# explain analyze select * from p1 where col3='30034'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Partition Iterator (cost=0.00..11.87 rows=2 width=23) (actual time=0.344..0.403 rows=2 loops=1) Iterations: 4 -> Partitioned Index Scan using idx_p1_col3 on p1 (cost=0.00..11.87 rows=2 width=23) (actual time=0.391..0.392 rows=2 loops=4) Index Cond: ((col3)::text = '30034'::text) Selected Partitions: 1..4 Total runtime: 0.439 ms, Peak Memory :154 (KB) (6 rows) ``` 虽然创建数据库时指定了Collate为C，在创建索引时也指定了collate为C，但在执行SQL时，并没有使用collate为C的索引（idx_p1_col2）。 其原因在于表列的collate和索引列的collate并不一致： ``` /* select t1.tablename,t2.idxname,t1.attname,t1.attcollation,t1.tabcolumncoll,t2.attcollation,t2.idxcolumncoll from (select a.attrelid::regclass as tablename,a.attname,a.attcollation,b.collname as tabcolumncoll from pg_attribute a,pg_collation b where a.attcollation = b.oid and a.attrelid IN ('p1'::regclass) ) as t1, (select a.attrelid::regclass as idxname,a.attname,a.attcollation,b.collname as idxcolumncoll from pg_attribute a,pg_collation b where a.attcollation = b.oid and a.attrelid in ('idx_p1_col2'::regclass,'idx_p1_col3'::regclass) ) as t2 where t1.attname = t2.attname; */ testdb=# select t1.tablename,t2.idxname,t1.attname,t1.attcollation,t1.tabcolumncoll,t2.attcollation,t2.idxcolumncoll testdb-# from (select a.attrelid::regclass as tablename,a.attname,a.attcollation,b.collname as tabcolumncoll testdb(# from pg_attribute a,pg_collation b testdb(# where a.attcollation = b.oid testdb(# and a.attrelid IN ('p1'::regclass) testdb(# ) as t1, testdb-# (select a.attrelid::regclass as idxname,a.attname,a.attcollation,b.collname as idxcolumncoll testdb(# from pg_attribute a,pg_collation b testdb(# where a.attcollation = b.oid testdb(# and a.attrelid in ('idx_p1_col2'::regclass,'idx_p1_col3'::regclass) testdb(# ) as t2 testdb-# where t1.attname = t2.attname; tablename | idxname | attname | attcollation | tabcolumncoll | attcollation | idxcolumncoll -----------+-------------+---------+--------------+---------------+--------------+--------------- p1 | idx_p1_col2 | col2 | 100 | default | 950 | C p1 | idx_p1_col3 | col3 | 100 | default | 100 | default (2 rows) ``` 950(C) vs 100(default)，虽然这个default也是C。