如何在SQLAlchemy中创建PostgreSQL多列trgm索引并实现多词查询？

一、创建基于两列的pg_trgm GIN索引 #

在SQLAlchemy中，可通过Index类直接定义带有gin_trgm_ops运算符的多列GIN索引，完全对应你提供的原生SQL逻辑：

from sqlalchemy import Column, Integer, String, Index, func
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    first_name = Column(String(50))
    last_name = Column(String(50))

# 定义多列GIN索引，与原生SQL创建语句一致
Index(
    'user_search_idx',
    func.first_name.op('gin_trgm_ops'),
    func.last_name.op('gin_trgm_ops'),
    using='gin'
)

二、多关键词模糊查询（所有关键词需匹配first_name或last_name） #

给定包含多个关键词的字符串（如word1 word2 word3），要实现「每个关键词都存在于first_name或last_name中，且不区分大小写」的查询，可按以下方式实现：

1. 拆分关键词并生成匹配条件 #

先拆分输入字符串为单个关键词，为每个关键词生成「匹配first_name或last_name」的条件，最后用AND连接所有条件，确保所有关键词都满足匹配规则：

from sqlalchemy import or_, and_
from sqlalchemy.orm import sessionmaker
# 假设已创建engine和Session实例
Session = sessionmaker(bind=engine)
session = Session()

# 输入的搜索字符串
search_input = "word1 word2 word3"
# 拆分并过滤空关键词
keywords = [kw.strip() for kw in search_input.split() if kw.strip()]

# 生成每个关键词的匹配条件
match_conditions = []
for keyword in keywords:
    # 结合pg_trgm索引的不区分大小写匹配，性能更优
    lower_kw = keyword.lower()
    cond = or_(
        func.lower(User.first_name).op('%')(lower_kw),
        func.lower(User.last_name).op('%')(lower_kw)
    )
    match_conditions.append(cond)

# 组合条件并执行查询
result = session.query(User).filter(and_(*match_conditions)).all()

可选：使用ILIKE语法 #

如果更倾向于传统ILIKE写法，可替换条件生成逻辑，但这种方式无法利用pg_trgm索引优化，数据量大时性能会下降：

like_pattern = f"%{keyword}%"
cond = or_(
    User.first_name.ilike(like_pattern),
    User.last_name.ilike(like_pattern)
)

内容的提问来源于stack exchange，提问作者tobias