PostgreSQL分区能否用于LIKE查询？大表模糊查询优化咨询

我有一张存储大量单词的words表，执行LIKE查询时耗时很长，索引优化效果不佳，因此尝试对word列进行范围分区：

create table words
(
  id   int,
  word varchar
) partition by RANGE (word);

CREATE TABLE words_1 PARTITION OF words
  FOR VALUES FROM ('a') TO ('n');

CREATE TABLE words_2 PARTITION OF words
  FOR VALUES FROM ('n') TO ('z');

注：实际计划为每个字母创建一个分区，示例中仅用两个分区简化说明。

分区在等值查询和大于/小于运算符下可正常工作：

explain
select * from words where word = 'abc'

执行计划：

Seq Scan on words_1 words  (cost=0.00..25.88 rows=6 width=36)
  Filter: ((word)::text = 'abc'::text)

explain
select * from words where word >= 'nth'

执行计划：

Seq Scan on words_2 words  (cost=0.00..25.88 rows=423 width=36)
  Filter: ((word)::text >= 'nth'::text)

但执行LIKE查询时，仍会扫描所有分区：

explain
select * from words where word LIKE 'abc%'

执行计划：

Append  (cost=0.00..51.81 rows=12 width=36)
  ->  Seq Scan on words_1  (cost=0.00..25.88 rows=6 width=36)
        Filter: ((word)::text ~~ 'abc'::text)
  ->  Seq Scan on words_2  (cost=0.00..25.88 rows=6 width=36)
        Filter: ((word)::text ~~ 'abc'::text)

请问是否可以让分区在LIKE查询中生效？或者有其他实现需求的方法？

1. 手动补充范围条件触发分区修剪 #

PostgreSQL的分区修剪逻辑不会自动将LIKE 'abc%'转换为对应的范围查询，但你可以手动添加等价的范围条件，让优化器识别并只扫描符合条件的分区：

explain
select * from words 
where word >= 'abc' 
  and word < 'abd'  -- 'abc'开头的字符串最大不会超过'abd'（字符串排序规则）
  and word LIKE 'abc%';

这样执行计划会只扫描words_1分区，因为范围['abc', 'abd')完全落在words_1的分区范围内。

2. 改用首字母LIST分区 #

如果你的查询大多是前缀匹配（比如xxx%），将分区策略从RANGE改为按首字母的LIST分区，更适合这类场景：

-- 创建主表，按首字母分区
CREATE TABLE words (
  id   int,
  word varchar
) PARTITION BY LIST (substring(word from 1 for 1));

-- 为每个字母创建分区
CREATE TABLE words_a PARTITION OF words FOR VALUES IN ('a');
CREATE TABLE words_b PARTITION OF words FOR VALUES IN ('b');
-- ... 依次创建c到z的分区

当执行WHERE word LIKE 'abc%'时，优化器能识别出首字母是'a'，只会扫描words_a分区，自动完成分区修剪。

3. 结合pg_trgm索引优化模糊查询性能 #

如果之前的索引优化效果不佳，可以尝试使用PostgreSQL的pg_trgm扩展创建GIN/GIST索引，这类索引对前缀、后缀、中间模糊查询都有很好的性能，配合分区使用效果更优：

-- 先安装pg_trgm扩展
CREATE EXTENSION pg_trgm;

-- 为每个分区创建GIN索引（GIN比GIST更适合精确前缀匹配）
CREATE INDEX idx_words_1_word_trgm ON words_1 USING GIN (word gin_trgm_ops);
CREATE INDEX idx_words_2_word_trgm ON words_2 USING GIN (word gin_trgm_ops);

即使分区修剪未完全生效，trgm索引也能大幅降低查询耗时；如果结合首字母LIST分区，还能同时利用分区修剪和索引扫描，达到最优性能。

4. 检查分区修剪参数 #

确保enable_partition_pruning参数已开启（PostgreSQL 10及以上版本默认开启），可以通过以下命令确认：

SHOW enable_partition_pruning;

如果未开启，执行SET enable_partition_pruning = on;（会话级）或在postgresql.conf中配置永久生效。

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