英文:
How to use case insensitive index in PostgreSQL in order to make performant reads via EF in C#?
问题
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: link.
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 a solution with EF.Functions.ILike. But worrying that ILike would not be as performant as creating a new case insensitive index.
EF.Functions.ILike(x.DEF, $"%{searchFor}%")
I did create a new index via pgAdmin
CREATE INDEX test1_lower_col1_idx ON "Manufacturing"."MyObjects" (lower("DEF"));
I read that the database would try to use my index automatically. I have dropped other index for the column DEF and restarted the PostgreSQL server service.
Trying to make a test select (via pgAdmin), but not sure if this works 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
doesn'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
results 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 comparison to using EF.Functions.ILike (regarding performance)?
英文:
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: https://stackoverflow.com/questions/7005302/how-to-make-case-insensitive-query-in-postgresql?rq=3#comment8364818_7005332.
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)?
答案1
得分: 2
The proper syntax is SELECT * FROM "制造"."终端" WHERE lower("序列号") LIKE lower('tj%');
EXPLAIN SELECT * FROM "制造"."我的对象" WHERE lower("我的列") LIKE lower('tj%');
Bitmap堆扫描在"我的对象"上 (成本=8.24..10.39 行=10 宽度=1755)
过滤器: (lower(("我的列")::text) ~~ 'tj%'::text)
-> Bitmap索引扫描在"IX_我的对象_我的列"上 (成本=0.00..8.23 行=10 宽度=0)
索引条件: ((lower(("我的列")::text) ~~ 'tj'::text) AND (lower(("我的列")::text) ~~ 'tk'::text))
限制:搜索文本不能以通配符开头。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论