Running on RDS with about 32M rows.
PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
Also testing locally on macOS with about 8M rows.
PostgreSQL 11.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit
I've got a column named old_value
that's of type citext. I asked about this already, but posted way to many of my discovery steps along the way. Here's a boiled down version that I'm hoping gets to the point.
Background
I've got a field change log table named record_changes_log_detail with 32M rows and growing that includes a citext field named old_value.
The data is very skeweed. Most values are less than a dozen characters, some are more than 5,000.
Postgres chokes on large values with an error about B-tree entries being limited to 2172 characters. So I believe that for a B-tree, I need to substring the source value.
My users primary interest is in an = search, a starts-with search, and, sometimes, a contains-this-substring search. So = string% and %string%
Goals
Create an index that supports those searches that the planner uses.
Tried and failed
A straight B-tree fails to build, in some cases, because of long values.
An expression B-tree like this builds, but is not used
CREATE INDEX record_changes_log_detail_old_value_ix_btree
ON record_changes_log_detail
USING btree (substring(old_value,1,1024));
Adding text_pattern_opts does not help.
CREATE INDEX record_changes_log_detail_old_value_ix_btree
ON record_changes_log_detail
USING btree (substring(old_value,1,1024) text_pattern_opts);
Tried and works partially
A hash index works, but only for equality. (Like it says on the tin.)
This is the closest I've gotten to success:
CREATE INDEX record_changes_log_detail_old_value_ix_btree
ON record_changes_log_detail
USING btree (old_value citext_pattern_ops);
This works for quality, but not for LIKE. The release notes for PG 11 say it should work for LIKE:
https://www.postgresql.org/docs/11/release-11.html
By "work" I mean "the index is used."
I was unable to substring succesfully with this approach.
What do people do in this situation with citext fields?