未使用數據類型 citext 的列上的索引

在 PostgreSQL 9.4 中，具有以下模式：

CREATE TABLE people (
   id INTEGER PRIMARY KEY,
   name TEXT,
   junk CHAR(1000)
);

INSERT INTO people(id, name)
SELECT generate_series(1,100000), md5(random()::text);

CREATE INDEX ON people (name text_pattern_ops);

如果我按名稱搜尋，則使用索引：

test=# explain analyze select id, name from people where name like 'a%';
                                                          QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on people  (cost=248.59..1160.92 rows=6061 width=37) (actual time=2.412..8.340 rows=6271 loops=1)
  Filter: (name ~~ 'a%'::text)
  Heap Blocks: exact=834
  ->  Bitmap Index Scan on people_name_idx  (cost=0.00..247.08 rows=6266 width=0) (actual time=2.123..2.123 rows=6271 loops=1)
        Index Cond: ((name ~>=~ 'a'::text) AND (name ~<~ 'b'::text))
Planning time: 0.600 ms
Execution time: 8.991 ms

但如果我替換TEXT為CITEXT：

CREATE EXTENSION CIText;

CREATE TABLE people (
   id INTEGER PRIMARY KEY,
   name CITEXT,
   junk CHAR(1000)
);

該索引不再使用：

test=# explain analyze select id, name from people where name like 'a%';
                                               QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
Seq Scan on people  (cost=0.00..2084.00 rows=500 width=36) (actual time=5.700..152.572 rows=6305 loops=1)
  Filter: (name ~~ 'a%'::citext)
  Rows Removed by Filter: 93695
Planning time: 0.764 ms
Execution time: 153.046 ms

根據CITEXTPostgreSQL 文件，行為應該與TEXT：

否則，它的行為幾乎與文本完全相同。

如何告訴 PostgreSQL 使用索引？

索引的使用text_pattern_ops（以及使用C語言環境時的預設運算符類）取決於字元數據的二進製表示。citext在保留大小寫的情況下儲存原始值，所以一定有問題……

就像您評論的那樣，實際原因隱藏在整理支持中。

無論哪種方式，citext或text，您都可以使其與表達式索引一起使用：

CREATE INDEX people_name_idx ON people (lower(name) text_pattern_ops);

以及相應的查詢：

SELECT id, name FROM people WHERE lower(name) LIKE 'abc%';

請注意，lower(name)返回數據類型text，即使在饋送時也是如此citext。

或者，您可以使用 trigram 索引，它的維護成本更高，但也提供更多功能：

• LIKE 是如何實現的？
• 在 PostgreSQL 中使用 LIKE、SIMILAR TO 或正則表達式進行模式匹配

另外：您的測試案例不是最理想的，因為您的虛擬值一開始都是小寫的，而且模式'a%'通常沒有足夠的選擇性來使用索引*。*而且char(1000)沒有意義（即使與測試無關）。

引用自：https://dba.stackexchange.com/questions/105244