如何在PostgreSQL中使用不区分大小写的索引,以便在C#中通过EF进行高性能读取?

ogq8wdun  于 2023-05-28  发布在  PostgreSQL
关注(0)|答案(1)|浏览(127)

我需要将我的阅读查询从PostgreSQL数据库上下文(通过C#和实体框架)切换为不区分大小写。

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

我想创建一个新的索引,如以下评论所示:如何在Postgresql中实现“不区分大小写”查询?.
使用PostgreSQL文档:

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

我已经尝试了StringComparison.CurrentCultureIgnoreCase,C#端的ToLower(),citext,排序规则,最后我用EF.Functions.ILike解决了这个问题。但是担心ILike的性能不如创建一个新的不区分大小写的索引。

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

我通过pgAdmin创建了一个新的索引

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

我读到数据库会尝试自动使用我的索引。我已经删除了列DEF的其他索引,并重新启动了PostgreSQL服务器服务。
尝试进行测试选择(通过pgAdmin),但不确定是否正确?假设我有一个值TJ004A0622510001

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

1.如果我有这个不区分大小写的索引,我是否还应该在列名周围使用lower()?

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

使用EXPLAIN不要给予我任何关于使用索引的提示…

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

也不是为了我的like目的:

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

将结果解释为(我猜没有使用索引):

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

1.我错过了什么?我应该如何为PostgreSQL设置一个不区分大小写的索引,以便我可以通过实体框架从C#代码进行查询,并且它会快速执行?
1.与使用EF.Functions.ILike相比,这样的场景是否更方便(就性能而言)?

c0vxltue

c0vxltue1#

正确的语法是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))

限制:搜索文本不能以通配符开头。

相关问题