PostgreSQL子查询后执行GROUP BY的性能及是否触发全表扫描问题

首先修正你提供的SQL语法问题 #

原SQL存在两处语法错误，可调整为如下可执行版本：

SELECT t1.user_name, SUM(t1.transaction_amount) total_txn
FROM (
    -- 子查询过滤指定日期数据
    SELECT year, month, day, user_name, transaction_amount 
    FROM "user" 
    WHERE year=2021 AND month = 3 AND day = 5 
) t1
GROUP BY t1.year, t1.month, t1.day, t1.user_name
ORDER BY total_txn DESC
LIMIT 10 OFFSET 10

注意：user是PostgreSQL的保留关键字，作为表名使用时建议加双引号包裹避免语法冲突。

核心问题解答 #

• 该查询不会触发全表扫描，子查询会优先过滤出小数据集，后续GROUP BY、ORDER BY、LIMIT逻辑都在该小数据集上执行。
• 原因是你的WHERE过滤条件year=2021 AND month = 3 AND day = 5完全命中了联合索引(year, month, day, user_name)的最左前缀匹配规则，PostgreSQL的查询优化器会直接走索引范围扫描定位到所有符合指定日期的行，不需要遍历全表的10亿条数据。
• 额外优化点：
  1. 你过滤后的所有数据的year、month、day都是固定值，GROUP BY可以简化为仅按user_name分组，执行逻辑完全一致，还能降低聚合计算开销。
  2. 如果要进一步提升性能，可以把transaction_amount加入联合索引，调整为(year, month, day, user_name, transaction_amount)的覆盖索引，这样查询不需要回表取交易金额字段，直接从索引就能拿到所有需要的查询数据，性能提升会非常明显。

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