PostgreSQL多关键词搜索查询未使用索引的优化方案咨询

AI 小北

2026-6-13

问题描述

我有一条PostgreSQL查询语句，无法有效利用索引。这条查询用于带AND条件的多关键词搜索（关键词用+分隔），要求返回的所有实体必须在指定的三个搜索字段中的至少一个包含全部关键词。我知道可以把concat这类重复操作移到CTE里提升性能，但当前核心问题是索引未被使用——表数据量极大，只有约1-2%的扫描行用到索引，其余全是全表扫描（Seq Scans）。

我试过把所有LOWER+LIKE替换成ILIKE并启用pg_trgm扩展，但通过pg_stat_user_tables查看，索引使用率并没有实质性提升。

查询语句

WITH search_terms AS (
    SELECT UNNEST(string_to_array(:searchTerm, '+')) AS term
)
SELECT ba.*, process_cell.owners
FROM business_asset ba
LEFT JOIN process_cell
  ON ba.process_cell_external_id = process_cell.external_id
WHERE ba.organization_id = :organizationId
  AND (
    :searchTerm IS NULL
    OR (
      (SELECT COUNT(*) FROM search_terms) = (
        SELECT COUNT(DISTINCT term)
        FROM search_terms st
        WHERE
          (
            POSITION('external_id' IN :searchFields) > 0 AND
            LOWER(ba.external_id) LIKE LOWER(CONCAT('%', replace(replace(st.term, '%', '\%'), '_', '\_'), '%'))
          )
          OR (
            POSITION('description' IN :searchFields) > 0 AND
            LOWER(ba.description) LIKE LOWER(CONCAT('%', replace(replace(st.term, '%', '\%'), '_', '\_'), '%'))
          )
          OR (
            POSITION('properties_yaml' IN :searchFields) > 0 AND
            LOWER(ba.properties_yaml) LIKE LOWER(CONCAT('%', replace(replace(st.term, '%', '\%'), '_', '\_'), '%'))
          )
      )
    )
  )
  AND (:types IS NULL OR ba.type = ANY(string_to_array(:types, ',')))
  AND (:owners IS NULL OR string_to_array(process_cell.owners, ',') && string_to_array(:owners, ','))
  AND (:country IS NULL OR LOWER(ba.country) = LOWER(:country))
  AND (:site IS NULL OR LOWER(ba.site) = LOWER(:site))
  AND (:area IS NULL OR LOWER(ba.area) = LOWER(:area))
  AND (:processCell IS NULL OR LOWER(ba.process_cell) = LOWER(:processCell))
  AND (
    :tags IS NULL OR ba.external_id LIKE ANY (
      SELECT val || '%' FROM unnest(string_to_array(:tags, ',')) AS val
    )
  )
ORDER BY
  CASE WHEN :sortField = 'createdAt' THEN ba.created_at END DESC,
  CASE WHEN :sortField = 'updatedAt' THEN ba.updated_at END DESC,
  CASE WHEN :sortField = 'externalId' THEN ba.external_id ELSE ba.external_id END
LIMIT :pageSize OFFSET :offset;

执行计划

Sort  (cost=51992.12..51994.93 rows=1123 width=483) (actual time=1423.959..1424.268 rows=5961 loops=1)
  Sort Key: ba.external_id
  Sort Method: quicksort  Memory: 2987kB
  CTE search_terms
    ->  ProjectSet  (cost=0.00..0.03 rows=2 width=32) (actual time=0.002..0.003 rows=2 loops=1)
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
  InitPlan 2 (returns $1)
    ->  Aggregate  (cost=0.04..0.06 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)
          ->  CTE Scan on search_terms  (cost=0.00..0.04 rows=2 width=0) (actual time=0.003..0.004 rows=2 loops=1)
  ->  Hash Left Join  (cost=14.50..51935.14 rows=1123 width=483) (actual time=0.410..1409.571 rows=5961 loops=1)
        Hash Cond: (ba.process_cell_external_id = process_cell.external_id)
        ->  Seq Scan on business_asset ba  (cost=0.00..51888.36 rows=1123 width=407) (actual time=0.405..1407.598 rows=5961 loops=1)
              Filter: ((organization_id = '4970f599-44ab-4bab-aee4-455b995fd22b'::uuid) AND ($1 = (SubPlan 3)))
              Rows Removed by Filter: 218557
              SubPlan 3
                ->  Aggregate  (cost=0.15..0.16 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=224518)
                      ->  Sort  (cost=0.14..0.15 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=224518)
                            Sort Key: st.term
                            Sort Method: quicksort  Memory: 25kB
                            ->  CTE Scan on search_terms st  (cost=0.00..0.13 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=224518)
"                                  Filter: ((lower(ba.external_id) ~~ lower(concat('%', replace(replace(term, '%'::text, '\%'::text), '_'::text, '\_'::text), '%'))) OR (lower(ba.description) ~~ lower(concat('%', replace(replace(term, '%'::text, '\%'::text), '_'::text, '\_'::text), '%'))) OR (lower(ba.properties_yaml) ~~ lower(concat('%', replace(replace(term, '%'::text, '\%'::text), '_'::text, '\_'::text), '%'))))"
                                  Rows Removed by Filter: 2
        ->  Hash  (cost=12.00..12.00 rows=200 width=64) (actual time=0.001..0.002 rows=0 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 8kB
              ->  Seq Scan on process_cell  (cost=0.00..12.00 rows=200 width=64) (actual time=0.001..0.001 rows=0 loops=1)
Planning Time: 0.321 ms
Execution Time: 1425.160 ms

请问应该使用哪些索引，以及如何改造查询语句让它使用这些索引？

解决方案

一、索引设计

1. 基础过滤字段的复合索引

organization_id是必选过滤条件，优先基于它构建复合索引，把高频过滤字段放在前面，减少扫描范围：

-- 覆盖基础过滤+常用排序字段，避免回表
CREATE INDEX idx_ba_org_core_filter ON business_asset (
    organization_id,
    type,
    lower(country),
    lower(site),
    lower(area),
    lower(process_cell)
) INCLUDE (external_id, created_at, updated_at);

如果部分过滤字段（如area、process_cell）使用频率低，可以调整顺序或移除，核心保留organization_id作为首字段。

2. pg_trgm模糊搜索索引

针对三个模糊搜索字段，创建基于lower()的trgm索引（GIN性能优、GIST省空间，按需选择）：

-- 启用pg_trgm扩展（如果未启用）
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- external_id的GIN索引（适合高频模糊搜索）
CREATE INDEX idx_ba_external_id_trgm ON business_asset USING GIN (lower(external_id) gin_trgm_ops);
-- description的GIN索引
CREATE INDEX idx_ba_description_trgm ON business_asset USING GIN (lower(description) gin_trgm_ops);
-- properties_yaml的GIST索引（大字段优先选GIST）
CREATE INDEX idx_ba_properties_yaml_trgm ON business_asset USING GIST (lower(properties_yaml) gist_trgm_ops);

3. tags前缀匹配索引

tags过滤是前缀匹配（LIKE 'val%'），用B-tree索引优化：

CREATE INDEX idx_ba_external_id_prefix ON business_asset (external_id text_pattern_ops);

4. process_cell关联索引

优化关联和owners过滤的索引，避免回表：

CREATE INDEX idx_pc_external_id_owners ON process_cell (external_id) INCLUDE (owners);

二、查询语句改造

1. 提前预处理计算，减少重复开销

把关键词转义、搜索字段判断等逻辑移到CTE，避免每行重复计算：

WITH search_terms AS (
    -- 提前处理关键词：转义特殊字符+转小写
    SELECT 
        lower(replace(replace(term, '%', '\%'), '_', '\_')) AS term
    FROM unnest(string_to_array(:searchTerm, '+')) AS term
),
search_fields AS (
    -- 提前计算搜索字段开关，避免WHERE里重复调用POSITION
    SELECT
        POSITION('external_id' IN :searchFields) > 0 AS search_external_id,
        POSITION('description' IN :searchFields) > 0 AS search_description,
        POSITION('properties_yaml' IN :searchFields) > 0 AS search_properties_yaml
),
term_count AS (
    -- 提前计算关键词总数
    SELECT COUNT(*) AS cnt FROM search_terms
)
SELECT ba.*, process_cell.owners
FROM business_asset ba
LEFT JOIN process_cell
  ON ba.process_cell_external_id = process_cell.external_id
CROSS JOIN search_fields sf
CROSS JOIN term_count tc
WHERE ba.organization_id = :organizationId
  AND (
    :searchTerm IS NULL
    OR (
      -- 用子查询统计匹配的关键词数，替代原有的逐行排序聚合
      tc.cnt = (
        SELECT COUNT(DISTINCT st.term)
        FROM search_terms st
        WHERE
          (sf.search_external_id AND lower(ba.external_id) LIKE CONCAT('%', st.term, '%'))
          OR (sf.search_description AND lower(ba.description) LIKE CONCAT('%', st.term, '%'))
          OR (sf.search_properties_yaml AND lower(ba.properties_yaml) LIKE CONCAT('%', st.term, '%'))
      )
    )
  )
  AND (:types IS NULL OR ba.type = ANY(string_to_array(:types, ',')))
  AND (:owners IS NULL OR string_to_array(process_cell.owners, ',') && string_to_array(:owners, ','))
  AND (:country IS NULL OR lower(ba.country) = lower(:country))
  AND (:site IS NULL OR lower(ba.site) = lower(:site))
  AND (:area IS NULL OR lower(ba.area) = lower(:area))
  AND (:processCell IS NULL OR lower(ba.process_cell) = lower(:processCell))
  AND (
    :tags IS NULL OR ba.external_id LIKE ANY (
      SELECT val || '%' FROM unnest(string_to_array(:tags, ',')) AS val
    )
  )
ORDER BY
  CASE WHEN :sortField = 'createdAt' THEN ba.created_at END DESC,
  CASE WHEN :sortField = 'updatedAt' THEN ba.updated_at END DESC,
  ba.external_id
LIMIT :pageSize OFFSET :offset;

2. 优化多关键词匹配逻辑（替代方案）

用JOIN+GROUP BY替代逐行子查询，让PostgreSQL能更好地利用trgm索引：

WITH search_terms AS (
    SELECT 
        lower(replace(replace(term, '%', '\%'), '_', '\_')) AS term
    FROM unnest(string_to_array(:searchTerm, '+')) AS term
),
search_fields AS (
    SELECT
        POSITION('external_id' IN :searchFields) > 0 AS search_external_id,
        POSITION('description' IN :searchFields) > 0 AS search_description,
        POSITION('properties_yaml' IN :searchFields) > 0 AS search_properties_yaml
),
term_count AS (
    SELECT COUNT(*) AS cnt FROM search_terms
)
SELECT ba.*, process_cell.owners
FROM business_asset ba
LEFT JOIN process_cell
  ON ba.process_cell_external_id = process_cell.external_id
CROSS JOIN search_fields sf
CROSS JOIN term_count tc
-- 仅当有搜索关键词时才关联搜索词表
LEFT JOIN search_terms st
  ON :searchTerm IS NOT NULL
  AND (
    (sf.search_external_id AND lower(ba.external_id) LIKE CONCAT('%', st.term, '%'))
    OR (sf.search_description AND lower(ba.description) LIKE CONCAT('%', st.term, '%'))
    OR (sf.search_properties_yaml AND lower(ba.properties_yaml) LIKE CONCAT('%', st.term, '%'))
  )
WHERE ba.organization_id = :organizationId
  AND (:searchTerm IS NULL OR COUNT(DISTINCT st.term) = tc.cnt)
  AND (:types IS NULL OR ba.type = ANY(string_to_array(:types, ',')))
  AND (:owners IS NULL OR string_to_array(process_cell.owners, ',') && string_to_array(:owners, ','))
  AND (:country IS NULL OR lower(ba.country) = lower(:country))
  AND (:site IS NULL OR lower(ba.site) = lower(:site))
  AND (:area IS NULL OR lower(ba.area) = lower(:area))
  AND (:processCell IS NULL OR lower(ba.process_cell) = lower(:processCell))
  AND (
    :tags IS NULL OR ba.external_id LIKE ANY (
      SELECT val || '%' FROM unnest(string_to_array(:tags, ',')) AS val
    )
  )
-- 按主键分组，确保聚合逻辑正确（假设ba.id是主键）
GROUP BY ba.id, process_cell.owners
ORDER BY
  CASE WHEN :sortField = 'createdAt' THEN ba.created_at END DESC,
  CASE WHEN :sortField = 'updatedAt' THEN ba.updated_at END DESC,
  ba.external_id
LIMIT :pageSize OFFSET :offset;

3. 简化大小写处理

如果业务允许，把country、site等字段存储为小写，直接去掉lower()函数；或者提前将查询参数转成小写传入，减少函数计算开销。

三、其他优化建议

更新表统计信息：执行ANALYZE business_asset;，让优化器获取最新数据分布，选择更优执行计划
避免大OFFSET分页：如果分页到较后位置，改用基于主键的游标分页，减少无效扫描
监控索引使用：用SELECT * FROM pg_stat_user_indexes WHERE relname = 'business_asset';查看索引实际使用率，调整索引策略

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

PostgreSQL多关键词搜索查询未使用索引的优化方案咨询

查询语句 #

执行计划 #

一、索引设计 #

1. 基础过滤字段的复合索引 #

2. pg_trgm模糊搜索索引 #

3. tags前缀匹配索引 #

4. process_cell关联索引 #

二、查询语句改造 #

1. 提前预处理计算，减少重复开销 #

2. 优化多关键词匹配逻辑（替代方案） #

3. 简化大小写处理 #

三、其他优化建议 #