4

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?

4
  • For reference, I went back to an excellent series of Postgres indexes: habr.com/en/company/postgrespro/blog/442776 The hashing function is exposed: select hashtext('Hello world!') -- -1587637769. Not sure what's actually stored in the index nodes...the hashing function is likely just helping get a bucket number? I'll have to dig a bit more... Sep 18, 2019 at 4:25
  • github.com/postgres/postgres/tree/master/src/backend/access/… If I read the read me for the source correctly (!), then the hashing function gets you to a bucket (by some steps), the bucket page includes the OID of the source row, and does not include the source value. That all sounds good. It also sounds like pages are never returned unless you REINDEX so, well, hmm. I'd rather use a B-tree in my case...but it just won't kick in :( Sep 18, 2019 at 4:35
  • github.com/postgres/postgres/blob/master/src/backend/access/… Sounds like the hash function result is a 32-bit value. Sep 18, 2019 at 4:40
  • For the record, I did a VACUUM ANALYZE on the table and the B-tree index is still ignored in a LIKE 'string%' search. Sep 18, 2019 at 8:47

3 Answers 3

2

It is unusual to index such a long column entirely.

Three ideas:

  1. Modify the query like this:

    WHERE substring(old_value, 1, 100) LIKE substring(pattern, 1, 100)
      AND old_value LIKE pattern
    

    (pattern here would be the pattern string, something like 'string%'.)

    Then a b-tree index on substring(old_value, 1, 100) can be used (if the pattern doesn't start with a wildcard character of course).

  2. Depending on the exact requirements (are you searching complete words or word prefixes in a natural language text or not), full text search may be a good solution.

  3. Another option are of course trigram indexes:

    CREATE INDEX ON record_changes_log_detail USING gin (old_value gin_trgm_ops);
    

    This requires the pg_trgm extension to be installed.

    Such an index will work also for search patterns that start with a wildcard. For good performance, enforce a minimum length on the search string.

3
  • Thanks for the answer! Yes, it's typically pointless to index such a large field. I'm used to doing so in another environment (priors === bad assumptions). In fact, all but a very few values are short or not long. Being a bit thick here, but what is pattern in your example above? And, yes, I've got to take another look at FTS and n-grams.There are lots of code fragments and error dumps in our text generally, so the last time I looked at stem-based FTS in Postgres it didn't help. N-grams are the greatest, so I'd be happy to have an excuse to use them in Postgres. Time to look again! Sep 19, 2019 at 7:08
  • I have added some explanations to the answer and added something about trigram indexes. Sep 19, 2019 at 7:18
  • Tri-grams rule! I love n-grams, and the Postgres implementation seems great. For those following along at home, the secret is to cast the value to ::text in your search, like this: select * from record_changes_log_detail where old_value::text LIKE '%Gold Kerrison Neuro%'; If you've built an index like Laurenz' specified, you can confirm with explain analyze that it's used. Fantastic! Sep 19, 2019 at 10:40
3

Please edit your question, rather than posting answers to it that don't answer it.

If you create an index on the expression substring(old_value,1,1024), then that index can only get used if you query involves substring(old_value,1,1024).

While it is theoretically possible to prove that old_value='foo' implies that substring(old_value,1,1024)='foo' (and thus the contrapositive to that) if you have enough insight into the internals of substring, PostgreSQL makes no attempt to prove that. You need to write the query in a way that no such proof is needed.

1
  • Yeah, my question(s) got out of control. I suck at writing short. I've boiled everything down into a summary instead. Sep 18, 2019 at 21:46
0

I'm back to close this question out. Following up on a suggestion from Laurenz Albe, I gave the Postgres tri-gram implementation a try. They rule!

DROP INDEX IF EXISTS record_changes_log_detail_old_value_ix_tgrm;
CREATE INDEX record_changes_log_detail_old_value_ix_tgrm
    ON record_changes_log_detail 
    USING gin (old_value gin_trgm_ops);

The secret here when you're using citext is to cast your value to ::text, like so:

select * from record_changes_log_detail 
where old_value::text LIKE '%Gold Kerrison Neuro%';

Running that with explain analyze confirms that the index is used. I noticed that I have to use LIKE for an = search, but that's okay.

1
  • I've faced simlar problems and tried this solution. I find that it makes to query case senstive again. So actually removes the point of it been a citext field. Sep 8, 2022 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.