Postgres中不区分大小写的SQLAlchemy索引

Question

考虑一个带有索引String字段的声明性SQLAlchemy模型：

class User(Base):
name = Column(String(100), index=True, nullable=False)

name字段区分大小写，这意味着应该保留原始大小写，但应该支持对索引进行高效的不区分大小写的查询。

实现这一点并在SQLAlchemy中实现的最佳方法是什么？

如果需要，查询可以使用lower()

session.query(User).filter_by(name=lower('SOME_name'))

但这并不重要，只要解决方案优雅且高性能即可。

由于性能要求，使用ILIKE和Postgres级别lower()的查询是不可接受的，它们已经过测试，对于我的用例来说，在大表上执行速度不够快。

Answer 1

创建一个函数索引，对表达式LOWER(name):进行索引

Index('idx_user_name_lower', func.lower(User.name))

使用索引查询(如(

session.query(User).filter(func.lower(User.name) == 'SOME_name'.lower())

如果LOWER(name)具有高基数。

然后，您可以在一个自定义比较器中封装处理降低成本：

# Verbatim from the documentation
class CaseInsensitiveComparator(Comparator):
def __eq__(self, other):
return func.lower(self.__clause_element__()) == func.lower(other)
class User(Base):
...
@hybrid_property
def name_insensitive(self):
return self.name.lower()
@name_insensitive.comparator
def name_insensitive(cls):
return CaseInsensitiveComparator(cls.name)

比较器将func.lower()应用于幕后双方：

session.query(User).filter_by(name_insensitive='SOME_name')

相当于

session.query(User).filter(func.lower(User.name) == func.lower('SOME_name'))