citext LIKE search bug

From: Morris de Oryx <morrisdeoryx(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: citext LIKE search bug
Date: 2019-09-19 00:32:08
Message-ID: CAKqncch4eBt2c8ddNVxzcVh3fFhdk54QoDMzpKgpqor4gA-wcQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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."

https://www.postgresql.org/docs/11/release-11.html

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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Morris de Oryx 2019-09-19 01:39:08 Fwd: citext LIKE search bug
Previous Message Jeff Janes 2019-09-18 20:31:05 Re: BUG #16012: vacuum full, something weird