I'm using citext fields, and am working through how to build indexes that the planner recognizes. I found this paragraph in the PG 11 release notes:
"Allow creation of indexes that can be used by LIKE comparisons on citext columns (Alexey Chernyshov)
To do this, the index must be created using the citext_pattern_ops operator class."
As far as I can tell, this doesn't work, or at least not in my case with an expression index. I've got a table with about 8M rows where the citext column contains anything from 1-5000+ characters. Since that's too big for a B-tree entry in some cases, I've got an expression index.
CREATE INDEX record_changes_log_detail_old_value_ix_btree
ON record_changes_log_detail
USING btree ((substring(old_value,1,1024)::citext) citext_pattern_ops);
The following = query *does* use the index:
set max_parallel_workers_per_gather = 0;
explain analyze
select * from record_changes_log_detail where substring(old_value,1,1024)::citext = 'Gold Kerrison Neuro';
The following LIKE query does *not* use the index:
set max_parallel_workers_per_gather = 0;
explain analyze
select * from record_changes_log_detail where substring(old_value,1,1024)::citext LIKE 'Gold Kerrison Neuro%';
Interestingly, if I build the index with text_pattern_ops, then these two queries *do* use the index:
select * from record_changes_log_detail where substring(old_value,1,1024) = 'Gold Kerrison Neuro';
select * from record_changes_log_detail where substring(old_value,1,1024) LIKE 'Gold Kerrison Neuro%';
So far as I can tell, the LIKE support for citext doesn't work. Or else I'm not understanding how to build the index or construct the query correctly.