索引与表的collate 不一致的情况下,会导致表上的索引不可用,这时要想使用索引,必须在SQL 语句指定建索引所用的collate。
数据库默认collate :
test=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+----------+-------------+-------------+-------------------
security | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/system +
| | | | | system=CTc/system
template1 | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/system +
| | | | | system=CTc/system
test | system | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
例子1:使用 collate "C" :表使用collate "C" , 则索引及查询语句默认也使用 collate "C" 这可以使用索引。
test=# create table t1(id text collate "C");
CREATE TABLE
test=# insert into t1 select generate_series(1000001,2000000);
INSERT 0 1000000
test=# create index idx_1 on t1(id);
CREATE INDEX
test=#
test=# analyze t1;
ANALYZE
test=# explain analyze select * from t1 where id='12345';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_1 on t1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.033..0.033 rows=0 loops=1)
Index Cond: (id = '12345'::text)
Heap Fetches: 0
Planning Time: 0.214 ms
Execution Time: 0.054 ms
(5 rows)
例子2:表使用 “C” ,索引使用 “POSIX” , 则SQL 默认使用 “C”。要想使用索引,SQL必须加 collate "POSIX";
test=# drop index idx_1;
DROP INDEX
test=#
test=# create index idx_1 on t1(id collate "POSIX");
CREATE INDEX
test=# explain analyze select * from t1 where id='12345';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..10633.43 rows=1 width=8) (actual time=42.802..42.948 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on t1 (cost=0.00..9633.33 rows=1 width=8) (actual time=34.985..34.987 rows=0 loops=3)
Filter: (id = '12345'::text)
Rows Removed by Filter: 333333
Planning Time: 0.226 ms
Execution Time: 42.973 ms
(8 rows)
test=#
test=# explain analyze select * from t1 where id='12345' collate "POSIX";
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_1 on t1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.093..0.094 rows=0 loops=1)
Index Cond: (id = '12345'::text COLLATE "POSIX")
Heap Fetches: 0
Planning Time: 0.095 ms
Execution Time: 0.116 ms
(5 rows)