PostgreSQL与Python分组性能对比：如何优化SQL查询效率？

问题背景 #

现有Transaction表包含字段：id（自增主键）、title（文本）、description（文本）、vendor（文本）。需求是提取表中出现频率最高的100个单词及二元词组合（排除重复组合与反向组合，如仅保留AB而非BA，排除AA），同时去除单词中的标点符号。

在20000条交易数据场景下，Python代码实现耗时约6-8秒，而PostgreSQL查询耗时达1分10秒，性能差距显著，需优化SQL查询。

现有PostgreSQL查询代码 #

WITH
    oneWord as (SELECT t.id, a.word, t.gross_amount
                FROM (SELECT * FROM transaction t) t,
                    unnest(string_to_array(regexp_replace(regexp_replace(
                        concat(t.vendor, ' ',
                             t.title, ' ',
                             t.description),
                      '[\s+]', ' ', 'g'), '[[:punct:]]', '', 'g'), ' ',
                '')) as a(word)
                WHERE a.word NOT IN (SELECT word FROM wordcloudexclusion)
    ),
    oneWordDistinct as (SELECT id, word, gross_amount FROM oneWord),
    twoWord as (SELECT a.id,CONCAT(a.word, ' ', b.word) as word, a.gross_amount
                from oneWord a, oneWord b
                where a.id = b.id and a < b),
    allWord as (SELECT oneWordDistinct.id as id, oneWordDistinct.word as word, oneWordDistinct.gross_amount as gross_amount
                from oneWordDistinct
                union all
                SELECT twoWord.id as id, twoWord.word as word, twoWord.gross_amount as gross_amount
                from twoWord)
SELECT a.word, count(a.id) FROM allWord a GROUP BY a.word ORDER BY 2 DESC LIMIT 100;

Python实现代码 #

text_stats = {}
transactions = (SELECT id, title, description, vendor, gross_amount FROM transactions)
for [id, title, description, vendor, amount] in list(transactions):

    text = " ".join(filter(None, [title, description, vendor]))
    text_without_punctuation = re.sub(r"[.!?,]+", "", text)
    text_without_tabs = re.sub(
        r"[\n\t\r]+", " ", text_without_punctuation
    ).strip(" ")
    words = list(set(filter(None, text_without_tabs.split(" "))))
    for a_word in words:
        if a_word not in excluded_words:
            if not text_stats.get(a_word):
                text_stats[a_word] = {
                    "count": 1,
                    "amount": amount,
                    "word": a_word,
                }
            else:
                text_stats[a_word]["count"] += 1
                text_stats[a_word]["amount"] += amount
            for b_word in words:
                if b_word > a_word:
                    sentence = a_word + " " + b_word
                    if not text_stats.get(sentence):
                        text_stats[sentence] = {
                            "count": 1,
                            "amount": amount,
                            "word": sentence,
                        }
                    else:
                        text_stats[sentence]["count"] += 1
                        text_stats[sentence]["amount"] += amount

SQL执行计划 #

Limit  (cost=260096.60..260096.85 rows=100 width=40) (actual time=63928.627..63928.639 rows=100 loops=1)
  CTE oneword
    ->  Nested Loop  (cost=16.76..2467.36 rows=44080 width=44) (actual time=1.875..126.778 rows=132851 loops=1)
          ->  Seq Scan on gc_api_transaction t  (cost=0.00..907.80 rows=8816 width=110) (actual time=0.018..4.176 rows=8816 loops=1)
                Filter: (company_id = 2)
                Rows Removed by Filter: 5648
          ->  Function Scan on unnest a_2  (cost=16.76..16.89 rows=5 width=32) (actual time=0.010..0.013 rows=15 loops=8816)
                Filter: (NOT (hashed SubPlan 1))
                Rows Removed by Filter: 2
                SubPlan 1
                  ->  Seq Scan on gc_api_wordcloudexclusion  (cost=0.00..15.40 rows=540 width=118) (actual time=1.498..1.500 rows=7 loops=1)
  ->  Sort  (cost=257629.24..257629.74 rows=200 width=40) (actual time=63911.588..63911.594 rows=100 loops=1)
        Sort Key: (count(oneword.id)) DESC
        Sort Method: top-N heapsort  Memory: 36kB
        ->  HashAggregate  (cost=257619.60..257621.60 rows=200 width=40) (actual time=23000.982..63803.962 rows=1194618 loops=1)
              Group Key: oneword.word
              Batches: 85  Memory Usage: 4265kB  Disk Usage: 113344kB
              ->  Append  (cost=0.00..241207.14 rows=3282491 width=36) (actual time=1.879..5443.143 rows=2868282 loops=1)
                    ->  CTE Scan on oneword  (cost=0.00..881.60 rows=44080 width=36) (actual time=1.878..579.936 rows=132851 loops=1)
"                    ->  Subquery Scan on ""*SELECT* 2""  (cost=13085.79..223913.09 rows=3238411 width=36) (actual time=2096.116..4698.727 rows=2735431 loops=1)"
                          ->  Merge Join  (cost=13085.79..191528.98 rows=3238411 width=44) (actual time=2096.114..4492.451 rows=2735431 loops=1)
                                Merge Cond: (a_1.id = b.id)
                                Join Filter: (a_1.* < b.*)
                                Rows Removed by Join Filter: 2879000
                                ->  Sort  (cost=6542.90..6653.10 rows=44080 width=96) (actual time=1088.083..1202.200 rows=132851 loops=1)
                                      Sort Key: a_1.id
                                      Sort Method: external merge  Disk: 8512kB
                                      ->  CTE Scan on oneword a_1  (cost=0.00..881.60 rows=44080 width=96) (actual time=3.904..101.754 rows=132851 loops=1)
                                ->  Materialize  (cost=6542.90..6763.30 rows=44080 width=96) (actual time=1007.989..1348.317 rows=5614422 loops=1)
                                      ->  Sort  (cost=6542.90..6653.10 rows=44080 width=96) (actual time=1007.984..1116.011 rows=132851 loops=1)
                                            Sort Key: b.id
                                            Sort Method: external merge  Disk: 8712kB
                                            ->  CTE Scan on oneword b  (cost=0.00..881.60 rows=44080 width=96) (actual time=0.014..20.998 rows=132851 loops=1)
Planning Time: 0.537 ms
JIT:
  Functions: 49
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 6.119 ms, Inlining 0.000 ms, Optimization 2.416 ms, Emission 17.764 ms, Total 26.299 ms"
Execution Time: 63945.718 ms

环境信息 #

• PostgreSQL版本：14.5 (Debian 14.5-1.pgdg110+1) on aarch64-unknown-linux-gnu

优化方案 #

1. 提前去重交易内重复单词 #

原SQL中oneWord未对同一交易内的重复单词去重，导致后续二元词生成时产生大量冗余组合。参考Python逻辑，在oneWord阶段添加DISTINCT去重：

oneWord as (
    SELECT DISTINCT t.id, a.word, t.gross_amount
    FROM transaction t,
         unnest(string_to_array(
             trim(regexp_replace(
                 concat(t.vendor, ' ', t.title, ' ', t.description),
                 '[\s[:punct:]]+', ' ', 'g'
             )), ' '
         )) as a(word)
    WHERE NOT EXISTS (SELECT 1 FROM wordcloudexclusion we WHERE we.word = a.word)
)

2. 替换笛卡尔积Join为LATERAL JOIN生成二元词 #

原SQL用笛卡尔积再过滤的方式会先生成所有可能组合，效率极低。改用LATERAL JOIN仅生成a.word < b.word的有效组合：

twoWord as (
    SELECT 
        t1.id,
        concat(t1.word, ' ', t2.word) as word,
        t1.gross_amount
    FROM oneWord t1
    JOIN LATERAL (
        SELECT word 
        FROM oneWord t2 
        WHERE t2.id = t1.id AND t2.word > t1.word
    ) t2 ON true
)

3. 合并正则表达式操作 #

将原两次regexp_replace合并为一次，减少函数调用开销，同时用trim()避免生成空单词：

trim(regexp_replace(
    concat(t.vendor, ' ', t.title, ' ', t.description),
    '[\s[:punct:]]+', ' ', 'g'
))

4. 优化排除词查询 #

• 给wordcloudexclusion表的word字段创建哈希索引：

  CREATE INDEX idx_wordcloudexclusion_word ON wordcloudexclusion USING hash(word);
  

• 用NOT EXISTS替代NOT IN，避免NULL值影响，且查询更稳定：

  WHERE NOT EXISTS (SELECT 1 FROM wordcloudexclusion we WHERE we.word = a.word)
  

5. 调整内存参数避免磁盘聚合 #

从执行计划看，HashAggregate使用了磁盘存储，临时调大work_mem让聚合在内存完成：

SET work_mem = '256MB'; -- 根据服务器内存情况调整，如512MB

优化后完整SQL示例 #

SET work_mem = '256MB';

WITH
    oneWord as (
        SELECT DISTINCT t.id, a.word, t.gross_amount
        FROM transaction t,
             unnest(string_to_array(
                 trim(regexp_replace(
                     concat(t.vendor, ' ', t.title, ' ', t.description),
                     '[\s[:punct:]]+', ' ', 'g'
                 )), ' '
             )) as a(word)
        WHERE NOT EXISTS (
            SELECT 1 FROM wordcloudexclusion we WHERE we.word = a.word
        )
    ),
    twoWord as (
        SELECT 
            t1.id,
            concat(t1.word, ' ', t2.word) as word,
            t1.gross_amount
        FROM oneWord t1
        JOIN LATERAL (
            SELECT word 
            FROM oneWord t2 
            WHERE t2.id = t1.id AND t2.word > t1.word
        ) t2 ON true
    ),
    allWord as (
        SELECT id, word, gross_amount FROM oneWord
        UNION ALL
        SELECT id, word, gross_amount FROM twoWord
    )
SELECT word, count(id) 
FROM allWord 
GROUP BY word 
ORDER BY count(id) DESC 
LIMIT 100;

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