无限滚动场景下用户查询的PostgreSQL索引设置咨询

现有索引的问题 #

• 表名不匹配：索引创建在tbl表上，但你的查询针对的是users表，这会导致索引完全无法被查询使用。
• GIN索引结构不合理：将普通列tenant_id与使用gin_trgm_ops的文本列放在同一个GIN索引中，GIN索引对普通等值过滤的支持远不如B-tree，无法高效利用tenant_id = $3的过滤条件，模糊查询的性能也会打折扣。
• 未覆盖必要字段：当前索引仅包含过滤字段，查询时需要回表获取id、e_types_id、company等字段，增加了IO开销。

优化后的索引方案 #

1. 修正表名并创建适配的GIN索引 #

针对你的查询过滤条件（tenant_id等值匹配 + firstname/lastname前后模糊匹配），创建适配的GIN索引：

CREATE INDEX users_tenant_name_trgm_idx ON users USING gin (tenant_id, firstname gin_trgm_ops, lastname gin_trgm_ops);

该索引可以同时满足tenant_id的等值过滤和firstname/lastname的模糊匹配需求，PostgreSQL能高效利用这个索引过滤符合条件的数据。

2. 可选：添加覆盖索引减少回表开销 #

如果查询性能仍有瓶颈，可以创建包含查询所需字段的覆盖索引（PostgreSQL 11+支持），避免回表操作：

CREATE INDEX users_tenant_name_covering_idx ON users USING gin (tenant_id, firstname gin_trgm_ops, lastname gin_trgm_ops)
INCLUDE (id, e_types_id, company);

这个索引包含了主查询需要的所有字段，查询时直接从索引中获取数据，无需访问主表。

查询语句优化（额外建议） #

当前查询中，子查询会为每一行结果重复计算一次总数，造成资源浪费。可以改用CROSS JOIN只计算一次总数：

SELECT 
  u.id,
  u.firstname, 
  u.lastname, 
  c.company, 
  et.name as employement_type,
  total.total_count
FROM users u 
LEFT JOIN employement_types et ON et.id = u.e_types_id 
LEFT JOIN clients c ON u.company = c.id 
CROSS JOIN (
  SELECT COUNT(id) as total_count FROM users 
  WHERE tenant_id = $3 
    AND (firstname LIKE '%'||$1||'%' OR lastname LIKE '%'||$1||'%')
) AS total
WHERE u.tenant_id = $3 
  AND (u.firstname LIKE '%'||$1||'%' OR u.lastname LIKE '%'||$1||'%') 
LIMIT 50 OFFSET $2

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