如何简化PostgreSQL去重拼接SQL查询以提升性能？

原查询语句 #

SELECT 
    t1.id AS id,
    array_to_string(
        array(
            SELECT DISTINCT unnest(
                string_to_array(
                    STRING_AGG(t2.words, '|')
                , '|')
            )
        )
    , '|') AS words
FROM libraries t1
JOIN library_file_relationships r ON t1.id = r.library_id
JOIN files t2 ON r.file_id = t2.id
GROUP BY t1.id

查询需求 #

从files表提取以|分隔的words字段，按libraries.id分组，生成仅包含唯一词的|分隔字符串。

简化优化方案 #

原查询先聚合所有字符串再拆分去重，会产生大量中间冗余数据，效率偏低。可以调整逻辑为先拆分、再去重、最后聚合，大幅减少不必要的操作：

优化后的查询语句 #

SELECT 
    t1.id AS id,
    STRING_AGG(DISTINCT word, '|') AS words
FROM libraries t1
JOIN library_file_relationships r ON t1.id = r.library_id
JOIN files t2 ON r.file_id = t2.id
JOIN unnest(string_to_array(t2.words, '|')) AS word
GROUP BY t1.id;

优化逻辑说明 #

1. 先用unnest(string_to_array(t2.words, '|'))将每个files.words字段拆分成单独的词行
2. 在聚合阶段直接通过DISTINCT对拆分后的词去重
3. 最后用STRING_AGG将去重后的词拼接为|分隔的字符串

如果files.words中可能存在空字符串或空白词，可添加过滤逻辑进一步清理结果：

SELECT 
    t1.id AS id,
    STRING_AGG(DISTINCT word, '|') AS words
FROM libraries t1
JOIN library_file_relationships r ON t1.id = r.library_id
JOIN files t2 ON r.file_id = t2.id
JOIN unnest(string_to_array(t2.words, '|')) AS word
WHERE trim(word) <> ''
GROUP BY t1.id;

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