How to use case insensitive index in PostgreSQL in order to make performant reads via EF in C#?

Question

I need to switch my reading query from PostgreSQL database context (via C# and Entity Framework) to be case-insensitive.

var list = _context.MyObjects.Where(x => !x.IsDeleted && (
                                    x.ABC.Contains(searchFor)
                                 || x.DEF.Contains(searchFor)
                                 || x.GHI.Contains(searchFor)))
                 .Select(x => new MyObject { ... });

I would like to create a new index as suggested in this comment: How to make "case-insensitive" query in Postgresql?.

Using PostgreSQL documentation:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

I have already tried StringComparison.CurrentCultureIgnoreCase, ToLower() on C# side, citext, collations and finally I have solution with EF.Functions.ILike. But worrying that ILike would be not as performant as creating a new case insensitive index.

EF.Functions.ILike(x.DEF, $"%{searchFor}%")

I did created a new index via pgAdmin

CREATE INDEX test1_lower_col1_idx ON "Manufacturing"."MyObjects" (lower("DEF"));

I read that database would try to use my index automatically. I have dropped other index for the column DEF and restarted PostgreSQL server service.

Trying to make test select (via pgAdmin), but not sure if this work correctly? Assume I have value TJ004A0622510001.

SELECT * FROM "Manufacturing"."MyObjects" WHERE "DEF" = 'tj004a0622510001';

Should I still use lower() surrounding the column name if I have this case insensitive index?

SELECT * FROM "Manufacturing"."MyObjects" WHERE lower("DEF") = 'tj004a0622510001';

Using EXPLAIN don't give me any clue about using my index...

Filter: (lower(("DEF")::text) = 'tj004a0622510001'::text)

Neither for my like purpose:

SELECT * FROM "Manufacturing"."MyObjects" WHERE LOWER("DEF") LIKE '%tj%' ESCAPE '';

explain resulting as (no index usage - I guess):

Filter: (lower(("DEF")::text) ~~ '%tj%'::text)

What have I missed? How should I set up a case insensitive index for PostgreSQL so I can make a query via Entity Framework from C# code and it would execute quickly?
Would such a scenario be more convenient in compare to using EF.Functions.ILike (regarding performance)?

dgad · Accepted Answer · 2023-05-25 13:55:31Z

2

The proper syntax is SELECT * FROM "Manufacturing"."Terminal" WHERE lower("SerialNumber") LIKE lower('tj%');

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))

Limitation: the search text can not be started with a wildcard.

answered May 25, 2023 at 13:55

dgad

656 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use case insensitive index in PostgreSQL in order to make performant reads via EF in C#?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related