I haven't reported a Postgres bug until now, please point me in the right direction if this isn't the right procedure for new reports.
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.
