Expression index on a citext column ignored, why?

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.

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.