如何优化返回嵌套JSON的SQL查询？性能调优求助

当前查询返回250条以上结果时耗时约10秒，导致无服务器函数超时，以下是针对性优化方案：

表信息与结构 #

数据规模 #

word        52000行
sense       70000行
inflection   1700行

表结构SQL #

create table word (id int8, level int2, homonymnumber int2, alpha int8, frequency int8, hangul text, hanja text, pronounciation text, audio text, typeeng text, typekr text);

create table inflection (inflectionid int8, id int8, inflections text, inflectionlinks json);

create table sense (senseid int8, id int8, translation text, definition text, krdefinition text, sensenr int2);

原查询SQL #

select json_agg(
        distinct jsonb_build_object(
            'item',
            jsonb_build_object(
                'Id',
                word.id,
                'Alpha',
                word.alpha,
                'Frequency',
                word.frequency,
                'HomonymNumber',
                word.homonymnumber,
                'Pronounciation',
                word.pronounciation,
                'Audio',
                word.audio,
                'TypeKr',
                word.typekr,
                'TypeEng',
                word.typeeng,
                'Hanja',
                word.hanja,
                'Hangul',
                word.hangul,
                'Senses',
                (
                    select json_agg(
                            jsonb_build_object(
                                'Translation',
                                sense.translation,
                                'Definition',
                                sense.definition,
                                'DefinitionKr',
                                sense.definitionkr,
                                'SenseNr',
                                sense.sensenr
                            )
                        )
                    from sense
                    where sense.id = word.id
                ),
                'Inflection',
                inflection.inflections,
                'InflectionLinks',
                inflection.inflectionlinks
            )
        )
    )
from word
    full join inflection on word.id = inflection.id
    right join sense on word.id = sense.id
where word.hangul similar to concat('%', term, '%')
    or sense.translation similar to concat('%', term, '%')
    or inflection.inflections similar to concat('%', term, '%');

优化方案 #

1. 重构JOIN逻辑，避免数据膨胀 #

原查询的full join + right join会导致数据重复，后续distinct去重又额外消耗资源。改为先收集所有匹配的word.id，再关联其他表：

WITH matched_ids AS (
    -- 收集所有符合条件的word id，自动去重
    SELECT id FROM word WHERE hangul ILIKE concat('%', term, '%')
    UNION
    SELECT id FROM sense WHERE translation ILIKE concat('%', term, '%')
    UNION
    SELECT id FROM inflection WHERE inflections ILIKE concat('%', term, '%')
)

2. 添加模糊查询索引，消除全表扫描 #

SIMILAR TO在%xxx%场景下性能极差，替换为ILIKE并添加trigram索引（需先安装pg_trgm扩展）：

-- 安装扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 创建字段索引
CREATE INDEX idx_word_hangul_trgm ON word USING GIST (hangul gist_trgm_ops);
CREATE INDEX idx_sense_translation_trgm ON sense USING GIST (translation gist_trgm_ops);
CREATE INDEX idx_inflection_inflections_trgm ON inflection USING GIST (inflections gist_trgm_ops);

索引创建后，ILIKE '%term%'会自动命中索引，大幅提升查询速度。

3. 重构JSON聚合逻辑，避免嵌套子查询 #

原查询中每个word都会触发一次sense表查询，改为预聚合sense数据后关联：

SELECT
    json_agg(
        jsonb_build_object(
            'item',
            jsonb_build_object(
                'Id', w.id,
                'Alpha', w.alpha,
                'Frequency', w.frequency,
                'HomonymNumber', w.homonymnumber,
                'Pronounciation', w.pronounciation,
                'Audio', w.audio,
                'TypeKr', w.typekr,
                'TypeEng', w.typeeng,
                'Hanja', w.hanja,
                'Hangul', w.hangul,
                'Senses', s.sense_list,
                'Inflection', i.inflections,
                'InflectionLinks', i.inflectionlinks
            )
        )
    )
FROM matched_ids m
JOIN word w ON m.id = w.id
-- 预聚合sense数据，仅执行一次
LEFT JOIN (
    SELECT id, json_agg(
        jsonb_build_object(
            'Translation', translation,
            'Definition', definition,
            'DefinitionKr', krdefinition,
            'SenseNr', sensenr
        )
    ) AS sense_list
    FROM sense
    GROUP BY id
) s ON w.id = s.id
LEFT JOIN inflection i ON w.id = i.id;

4. 移除冗余的DISTINCT #

通过UNION获取的matched_ids已自动去重，后续关联不会产生重复的word数据，因此可以移除原查询中的distinct关键字。

5. 可选：分页减少单次查询数据量 #

如果后端允许分页，可添加LIMIT和OFFSET（或keyset分页），降低单次查询的处理压力：

-- 在主查询末尾添加
LIMIT 100 OFFSET 0;

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