0

Using PostgreSQL, EntityFramework, C#.

My index was created by:

CREATE UNIQUE INDEX "IX_MyObjects_MyColumn"
ON "Manufacturing"."MyObjects" USING btree
(LOWER("MyColumn") COLLATE pg_catalog."default" varchar_pattern_ops ASC NULLS LAST)
TABLESPACE pg_default;

and it is working as expected for like query with a wildcard:

EXPLAIN SELECT * FROM "Manufacturing"."MyObjects" WHERE lower("MyColumn") LIKE lower('tj%');

Bitmap Heap Scan on "MyObjects"  (cost=8.24..10.39 rows=10 width=1755)
  Filter: (lower(("MyColumn")::text) ~~ 'tj%'::text)
  ->  Bitmap Index Scan on "IX_MyObjects_MyColumn"  (cost=0.00..8.23 rows=10 width=0)
        Index Cond: ((lower(("MyColumn")::text) ~>=~ 'tj'::text) AND (lower(("MyColumn")::text) ~<~ 'tk'::text))

but only if it is not a starting character in a search string.

EXPLAIN SELECT * FROM "Manufacturing"."MyObjects" WHERE lower("MyColumn") LIKE lower('%tj%');

Seq Scan on "MyObjects"  (cost=10000000000.00..10000000002.15 rows=3 width=1755)
  Filter: (lower(("MyColumn")::text) ~~ '%tj%'::text)

Is there any way to make it working for search text with starting wildcard?

Limiting the substring search to only the ones with a correct prefix is not acceptable from the perspective of requirements and best user experience.

dgad
  • 41
  • 4
  • 2
    Check the extension pg_trgm, this has support for your type of query: https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11.7.44.8 – Frank Heikens May 25 '23 at 13:42
  • A standard B-tree index starts from the left of the string, so no, you can't preface an index with wildcard, using a normal B-tree index. – Tim Biegeleisen May 25 '23 at 13:44
  • 1
    You could index the reversed string and test with a reversed pattern, but since your example has a wildcard on each end that won't work. And pg_trgm won't work well either, since your example pattern has no trigrams in it. – jjanes May 25 '23 at 14:59
  • Trigram did work for me. Thank you @FrankHeikens for the hint. – dgad May 29 '23 at 07:36
  • @jjanes As I read today - trigram matching adds space at the front and the end so it can match text with lower sizes than 3 characters. FYI. Regards – dgad May 29 '23 at 07:37
  • 1
    @dgad but that only helps if the space (or punctuation, or string boundary) is in the pattern right next to the character. It doesn't add spaces (for the pattern side) when the wild card is abutting the characters. – jjanes May 29 '23 at 08:32
  • @jjanes you are right, trigram index does not fire for something like `... WHERE lower("DEF") LIKE '%tj%';` while for `tj%` it does. I was sure I have checked it before and obtained the positive outcome in both version. Thank you for this comment a lot! – dgad May 29 '23 at 09:38
  • @jjanes actually to be precise - I have managed the postgres to use trigram index for `%tj%` by executing `set enable_seqscan = off;` in front of the query, but this does not give significant performance boost. FYI. Thank you for discussion. – dgad May 29 '23 at 09:46

0 Answers0