如何在无复合索引下优化大PostgreSQL表的搜索查询？

1. 用精确匹配列缩小数据集，轻量索引降低写入影响 #

你的查询里tenant_id和name都是精确匹配（=条件），过滤性极强，先给它们建复合索引：

CREATE INDEX idx_users_tenant_name ON users (tenant_id, name);

这个B树索引的维护开销极低——写入时仅需维护有序键值，对INSERT/UPDATE/DELETE的性能影响微乎其微。查询时PostgreSQL会先通过该索引快速定位到符合tenant_id='tenant_1234' AND name='raju'的行，再在这个小数据集内做company和description的模糊匹配，整体速度会大幅提升。

2. 用全文搜索替代LIKE '%xxx%'，避免全表扫描 #

LIKE '%kg%'这类前后通配的模糊查询无法使用普通B树索引，会触发全表扫描。PostgreSQL自带的全文搜索可高效处理这类包含性匹配，步骤如下：

• 新增生成列，将需要模糊搜索的列合并为全文向量：

  ALTER TABLE users ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
    to_tsvector('english', company || ' ' || description)
  ) STORED;
  

• 给生成列建GIN索引：

  CREATE INDEX idx_users_search_vector ON users USING GIN (search_vector);
  

• 查询时改用全文搜索语法：

  SELECT * 
  FROM users 
  WHERE tenant_id = 'tenant_1234' 
    AND name = 'raju'
    AND search_vector @@ to_tsquery('english', 'kg & ght');
  

GIN索引的写入开销略高于B树，但远低于维护多列复合索引；生成列由数据库自动维护，无需额外代码。若对写入性能有极致要求，可改用GIST索引（写入更快，查询稍慢）。

3. 按tenant_id做表分区，直接削减扫描范围 #

所有查询都带tenant_id条件，按tenant_id做列表分区（若tenant_id有序，也可用范围分区）：

• 创建分区表：

  CREATE TABLE users (
    id INT,
    tenant_id VARCHAR,
    name VARCHAR,
    company VARCHAR,
    location VARCHAR,
    description TEXT
  ) PARTITION BY LIST (tenant_id);
  

• 为常用租户创建分区：

  CREATE TABLE users_tenant_1234 PARTITION OF users FOR VALUES IN ('tenant_1234');
  

分区表的写入性能与普通表几乎无差异——写入时仅需定位到对应分区，无需额外索引维护开销。查询时PostgreSQL只会扫描目标租户的分区，数据量从几千万直接降到单个租户规模，查询效率会显著提升。

4. 调整数据库配置，优化内存使用 #

针对大表查询，调整PostgreSQL核心配置参数：

• 加大work_mem：比如设为64MB，让模糊搜索、排序等操作在内存中完成，避免磁盘临时文件交换。
• 调整shared_buffers：设为服务器内存的25%-50%（如32G内存的服务器设为8G），让更多热点数据留在内存，减少磁盘IO。
  这些配置调整完全不影响写入性能，仅提升查询效率。

5. 避免SELECT *，只查询业务需要的列 #

SELECT *会返回所有列数据，尤其是description这类大文本列，会增加数据传输和内存占用。改成只查询所需列：

SELECT id, name, company, location 
FROM users 
WHERE ...;

这能显著减少查询IO开销，提升响应速度，且对写入无任何影响。

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