优化PostgreSQL千万级数据查询：关键词与商标匹配性能提升

原查询的核心问题 #

你当前的查询需要对3000万条words数据的keyword字段重复执行string_to_array拆分，且无法利用索引完成关联匹配，导致全表扫描和大量计算，这是性能瓶颈的根源。下面是针对性的优化方案：

方案1：预拆分关键词并建立关联表（最适合超大数据量场景） #

将words表的keyword字段提前拆分为单个单词存储到关联表，避免每次查询时重复拆分，同时为拆分后的单词建立索引：

1. 创建单词拆分关联表

CREATE TABLE word_tokens (
    word_id bigint REFERENCES words(id),
    token text NOT NULL,
    PRIMARY KEY (word_id, token)
);

2. 批量插入拆分后的单词数据

INSERT INTO word_tokens (word_id, token)
SELECT id, unnest(string_to_array(keyword, ' '))
FROM words;

3. 为拆分后的单词建立索引

CREATE INDEX idx_word_tokens_token ON word_tokens(token);

4. 优化后的查询语句

SELECT w.id, w.keyword, t.trademark
FROM words w
JOIN word_tokens wt ON w.id = wt.word_id
JOIN trademarks t ON wt.token = t.trademark
WHERE wt.token = 'all';

这个方案通过预计算拆分结果，将原查询的动态字符串操作转为索引关联查询，能大幅降低计算量，3000万条数据的场景下性能提升显著。

方案2：使用生成列+GIN索引（无需额外表，适合PostgreSQL 12+） #

利用PostgreSQL的生成列功能，将keyword的拆分结果持久化，并为数组字段建立GIN索引：

1. 添加生成列存储拆分后的单词数组

ALTER TABLE words ADD COLUMN tokens text[] GENERATED ALWAYS AS (string_to_array(keyword, ' ')) STORED;

2. 为数组字段建立GIN索引

CREATE INDEX idx_words_tokens ON words USING GIN(tokens);

3. 优化后的查询语句

SELECT w.id, w.keyword, t.trademark
FROM words w
JOIN trademarks t ON t.trademark = ANY(w.tokens)
WHERE 'all' = ANY(w.tokens);

GIN索引对数组的ANY匹配有很好的支持，避免了全表扫描，同时无需维护额外表，适合不想修改数据结构的场景。

基础优化：为商标表建立索引 #

无论采用哪种方案，都需要先为trademarks表的trademark字段建立索引，因为商标表只有数千条数据，索引能让匹配查询瞬间完成：

CREATE UNIQUE INDEX idx_trademarks_trademark ON trademarks(trademark);

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