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。