1. 问题背景与性能差异现象最近在GaussDB环境中遇到一个典型的分析函数性能问题同样的SQL查询在Oracle 19.13上执行仅需0.928秒而在GaussDB 506.0上却需要2765毫秒约2.765秒性能差距达到3倍。更令人意外的是原生PostgreSQL 18.1也需要1806毫秒约1.8秒是Oracle的两倍。测试用例是一个包含50万行数据的表结构如下CREATE TABLE your_table ( group_col1 NUMERIC, group_col2 VARCHAR(20), group_col3 VARCHAR(20), sort_col1 TIMESTAMP(0), sort_col2 NUMERIC );核心查询是通过ROW_NUMBER()分析函数获取每个分组的最新记录SELECT group_col1, group_col2, group_col3, sort_col1, sort_col2 FROM ( SELECT group_col1, group_col2, group_col3, sort_col1, sort_col2, ROW_NUMBER() OVER ( PARTITION BY group_col1, group_col2, group_col3 ORDER BY sort_col1 DESC, sort_col2 DESC ) AS rn FROM your_table ) WHERE rn 1;2. 性能瓶颈分析与定位2.1 排序操作的影响通过去除ORDER BY子句的对比测试发现性能差异主要来自排序操作Oracle去除排序后耗时从928ms降至21msGaussDB去除排序后耗时从2765ms降至1ms这表明分析函数的主要开销确实在排序阶段且GaussDB的排序性能影响比Oracle更为显著。2.2 执行计划对比分析GaussDB的执行计划发现优化器选择了Hash Anti Join方案Hash Anti Join (cost16744.66..59958.20 rows148155 width28) Hash Cond: ((t1.group_col1 t2.group_col1) AND ...) Join Filter: ((t2.sort_col1 t1.sort_col1) OR ...) Rows Removed by Join Filter: 500047 - Seq Scan on your_table t1 - Hash - Seq Scan on your_table t2而Oracle的执行计划显示其优化器能更高效地处理分析函数SELECT STATEMENT WINDOW SORT TABLE ACCESS FULL3. SQL改写优化方案3.1 传统NOT EXISTS改写针对GaussDB的特性采用NOT EXISTS方式重写SQLSELECT t1.* FROM your_table t1 WHERE NOT EXISTS ( SELECT 1 FROM your_table t2 WHERE t2.group_col1 t1.group_col1 AND t2.group_col2 t1.group_col2 AND t2.group_col3 t1.group_col3 AND ( t2.sort_col1 t1.sort_col1 OR (t2.sort_col1 t1.sort_col1 AND t2.sort_col2 t1.sort_col2) OR (t2.sort_col1 t1.sort_col1 AND t2.sort_col2 t1.sort_col2 AND t2.ctid t1.ctid) ) );改写后的性能GaussDB1070ms接近Oracle原性能PostgreSQL无并行超过30分钟Oracle超过30分钟无法完成3.2 改写原理详解这种改写方式的核心逻辑是通过NOT EXISTS反关联自身表关联条件包含分组字段group_col1/2/3排序逻辑转换为显式比较先比较sort_col1sort_col1相等时比较sort_col2两个字段都相等时用ctid/rowid保证确定性最终保留每组中没有比它更大的记录的那一行注意ctid是PostgreSQL/GaussDB特有的物理行标识Oracle中应改用rowid。此方法不适用于分区表分区表需要额外考虑tableoid字段。3.3 Oracle专属优化方案针对Oracle环境可以采用KEEP FIRST语法获得更好性能SELECT group_col1, group_col2, group_col3, to_char(sort_col1, yyyy-mm-dd hh24:mi:ss) sort_col1, sort_col2 FROM your_table WHERE rowid IN ( SELECT MIN(rowid) KEEP(DENSE_RANK FIRST ORDER BY sort_col1 DESC, sort_col2 DESC) FROM your_table GROUP BY group_col1, group_col2, group_col3 );此方案在Oracle上仅需208ms比原分析函数方案快4倍多。4. 各数据库表现差异分析4.1 GaussDB的优化策略GaussDB默认关闭了mergejoin和nestloop这是基于大量生产环境调优的结果-- 查看默认设置 SHOW enable_mergejoin; -- 通常为off SHOW enable_nestloop; -- 通常为off这种配置使得GaussDB在面对此类查询时能自动选择更优的Hash Anti Join方案而原生PostgreSQL在没有并行查询的情况下会陷入性能困境。4.2 PostgreSQL的并行查询优势当开启并行查询时默认配置PostgreSQL 18.1也能在1307ms内完成查询Gather (cost12034.44..49420.09 rows148155 width28) Workers Planned: 2 Workers Launched: 2 - Parallel Hash Anti Join但关闭并行后由于选择了低效的Merge Anti Join导致性能急剧下降Merge Anti Join (cost139366.21..324181778.65 rows148155 width28) - Sort - Materialize - Sort4.3 DuckDB的极端表现测试中还发现DuckDB 1.4.1的有趣现象原分析函数查询100ms比Oracle快9倍NOT EXISTS改写内存爆满无法完成这说明不同数据库对相同SQL的实现方式存在本质差异。5. 生产环境优化建议5.1 GaussDB优化方案选择优先方案使用NOT EXISTS改写性能接近Oracle原版代码可读性较好注意添加分组字段的联合索引备选方案使用Oracle风格的KEEP FIRST语法SELECT * FROM your_table WHERE ctid IN ( SELECT min(ctid) OVER ( PARTITION BY group_col1, group_col2, group_col3 ORDER BY sort_col1 DESC, sort_col2 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM your_table );5.2 索引设计建议虽然理论上可以创建覆盖索引CREATE INDEX idx_covering ON your_table ( group_col1, group_col2, group_col3, sort_col1 DESC, sort_col2 DESC );但实际上由于涉及DESC排序和复合条件这样的函数索引可能带来维护开销需谨慎评估。5.3 参数调优参考对于PostgreSQL环境可以调整这些参数改善性能SET enable_mergejoin off; SET enable_nestloop off; SET work_mem 64MB; -- 增加排序可用内存6. 深度思考与经验总结数据库差异的本质SQL虽然表面是标准语言但各数据库的实现优化器、执行引擎差异巨大性能可能相差几个数量级。这就像同样的C代码在不同编译器下性能不同。优化器不能完全信赖即使现代优化器很强大特定场景下仍需人工干预。我曾遇到一个案例GaussDB对NOT EXISTS的代价估算比实际低两个数量级。改写策略的普适性本文的NOT EXISTS改写虽然在GaussDB有效但在Oracle上却灾难性性能下降。这说明没有放之四海而皆准的优化方案。性能对比的公平性比较数据库性能时要确保测试数据特征一致使用各数据库最优的SQL写法考虑默认配置差异分析函数的替代方案除了本文方案还可以考虑-- 方案1使用DISTINCT ONPostgreSQL特有 SELECT DISTINCT ON (group_col1, group_col2, group_col3) * FROM your_table ORDER BY group_col1, group_col2, group_col3, sort_col1 DESC, sort_col2 DESC; -- 方案2使用LATERAL JOIN SELECT t.* FROM ( SELECT DISTINCT group_col1, group_col2, group_col3 FROM your_table ) g JOIN LATERAL ( SELECT * FROM your_table t WHERE t.group_col1 g.group_col1 AND t.group_col2 g.group_col2 AND t.group_col3 g.group_col3 ORDER BY sort_col1 DESC, sort_col2 DESC LIMIT 1 ) t ON true;最后分享一个实际排查中的技巧当遇到性能差异时先简化SQL如去掉排序、减少分组字段逐步定位瓶颈点比直接分析复杂SQL更高效。 
长河君 · 2026/10/30 8:42:41 · 阅读 1.2k GaussDB与Oracle分析函数性能差异及优化实践
1. 问题背景与性能差异现象最近在GaussDB环境中遇到一个典型的分析函数性能问题同样的SQL查询在Oracle 19.13上执行仅需0.928秒而在GaussDB 506.0上却需要2765毫秒约2.765秒性能差距达到3倍。更令人意外的是原生PostgreSQL 18.1也需要1806毫秒约1.8秒是Oracle的两倍。测试用例是一个包含50万行数据的表结构如下CREATE TABLE your_table ( group_col1 NUMERIC, group_col2 VARCHAR(20), group_col3 VARCHAR(20), sort_col1 TIMESTAMP(0), sort_col2 NUMERIC );核心查询是通过ROW_NUMBER()分析函数获取每个分组的最新记录SELECT group_col1, group_col2, group_col3, sort_col1, sort_col2 FROM ( SELECT group_col1, group_col2, group_col3, sort_col1, sort_col2, ROW_NUMBER() OVER ( PARTITION BY group_col1, group_col2, group_col3 ORDER BY sort_col1 DESC, sort_col2 DESC ) AS rn FROM your_table ) WHERE rn 1;2. 性能瓶颈分析与定位2.1 排序操作的影响通过去除ORDER BY子句的对比测试发现性能差异主要来自排序操作Oracle去除排序后耗时从928ms降至21msGaussDB去除排序后耗时从2765ms降至1ms这表明分析函数的主要开销确实在排序阶段且GaussDB的排序性能影响比Oracle更为显著。2.2 执行计划对比分析GaussDB的执行计划发现优化器选择了Hash Anti Join方案Hash Anti Join (cost16744.66..59958.20 rows148155 width28) Hash Cond: ((t1.group_col1 t2.group_col1) AND ...) Join Filter: ((t2.sort_col1 t1.sort_col1) OR ...) Rows Removed by Join Filter: 500047 - Seq Scan on your_table t1 - Hash - Seq Scan on your_table t2而Oracle的执行计划显示其优化器能更高效地处理分析函数SELECT STATEMENT WINDOW SORT TABLE ACCESS FULL3. SQL改写优化方案3.1 传统NOT EXISTS改写针对GaussDB的特性采用NOT EXISTS方式重写SQLSELECT t1.* FROM your_table t1 WHERE NOT EXISTS ( SELECT 1 FROM your_table t2 WHERE t2.group_col1 t1.group_col1 AND t2.group_col2 t1.group_col2 AND t2.group_col3 t1.group_col3 AND ( t2.sort_col1 t1.sort_col1 OR (t2.sort_col1 t1.sort_col1 AND t2.sort_col2 t1.sort_col2) OR (t2.sort_col1 t1.sort_col1 AND t2.sort_col2 t1.sort_col2 AND t2.ctid t1.ctid) ) );改写后的性能GaussDB1070ms接近Oracle原性能PostgreSQL无并行超过30分钟Oracle超过30分钟无法完成3.2 改写原理详解这种改写方式的核心逻辑是通过NOT EXISTS反关联自身表关联条件包含分组字段group_col1/2/3排序逻辑转换为显式比较先比较sort_col1sort_col1相等时比较sort_col2两个字段都相等时用ctid/rowid保证确定性最终保留每组中没有比它更大的记录的那一行注意ctid是PostgreSQL/GaussDB特有的物理行标识Oracle中应改用rowid。此方法不适用于分区表分区表需要额外考虑tableoid字段。3.3 Oracle专属优化方案针对Oracle环境可以采用KEEP FIRST语法获得更好性能SELECT group_col1, group_col2, group_col3, to_char(sort_col1, yyyy-mm-dd hh24:mi:ss) sort_col1, sort_col2 FROM your_table WHERE rowid IN ( SELECT MIN(rowid) KEEP(DENSE_RANK FIRST ORDER BY sort_col1 DESC, sort_col2 DESC) FROM your_table GROUP BY group_col1, group_col2, group_col3 );此方案在Oracle上仅需208ms比原分析函数方案快4倍多。4. 各数据库表现差异分析4.1 GaussDB的优化策略GaussDB默认关闭了mergejoin和nestloop这是基于大量生产环境调优的结果-- 查看默认设置 SHOW enable_mergejoin; -- 通常为off SHOW enable_nestloop; -- 通常为off这种配置使得GaussDB在面对此类查询时能自动选择更优的Hash Anti Join方案而原生PostgreSQL在没有并行查询的情况下会陷入性能困境。4.2 PostgreSQL的并行查询优势当开启并行查询时默认配置PostgreSQL 18.1也能在1307ms内完成查询Gather (cost12034.44..49420.09 rows148155 width28) Workers Planned: 2 Workers Launched: 2 - Parallel Hash Anti Join但关闭并行后由于选择了低效的Merge Anti Join导致性能急剧下降Merge Anti Join (cost139366.21..324181778.65 rows148155 width28) - Sort - Materialize - Sort4.3 DuckDB的极端表现测试中还发现DuckDB 1.4.1的有趣现象原分析函数查询100ms比Oracle快9倍NOT EXISTS改写内存爆满无法完成这说明不同数据库对相同SQL的实现方式存在本质差异。5. 生产环境优化建议5.1 GaussDB优化方案选择优先方案使用NOT EXISTS改写性能接近Oracle原版代码可读性较好注意添加分组字段的联合索引备选方案使用Oracle风格的KEEP FIRST语法SELECT * FROM your_table WHERE ctid IN ( SELECT min(ctid) OVER ( PARTITION BY group_col1, group_col2, group_col3 ORDER BY sort_col1 DESC, sort_col2 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM your_table );5.2 索引设计建议虽然理论上可以创建覆盖索引CREATE INDEX idx_covering ON your_table ( group_col1, group_col2, group_col3, sort_col1 DESC, sort_col2 DESC );但实际上由于涉及DESC排序和复合条件这样的函数索引可能带来维护开销需谨慎评估。5.3 参数调优参考对于PostgreSQL环境可以调整这些参数改善性能SET enable_mergejoin off; SET enable_nestloop off; SET work_mem 64MB; -- 增加排序可用内存6. 深度思考与经验总结数据库差异的本质SQL虽然表面是标准语言但各数据库的实现优化器、执行引擎差异巨大性能可能相差几个数量级。这就像同样的C代码在不同编译器下性能不同。优化器不能完全信赖即使现代优化器很强大特定场景下仍需人工干预。我曾遇到一个案例GaussDB对NOT EXISTS的代价估算比实际低两个数量级。改写策略的普适性本文的NOT EXISTS改写虽然在GaussDB有效但在Oracle上却灾难性性能下降。这说明没有放之四海而皆准的优化方案。性能对比的公平性比较数据库性能时要确保测试数据特征一致使用各数据库最优的SQL写法考虑默认配置差异分析函数的替代方案除了本文方案还可以考虑-- 方案1使用DISTINCT ONPostgreSQL特有 SELECT DISTINCT ON (group_col1, group_col2, group_col3) * FROM your_table ORDER BY group_col1, group_col2, group_col3, sort_col1 DESC, sort_col2 DESC; -- 方案2使用LATERAL JOIN SELECT t.* FROM ( SELECT DISTINCT group_col1, group_col2, group_col3 FROM your_table ) g JOIN LATERAL ( SELECT * FROM your_table t WHERE t.group_col1 g.group_col1 AND t.group_col2 g.group_col2 AND t.group_col3 g.group_col3 ORDER BY sort_col1 DESC, sort_col2 DESC LIMIT 1 ) t ON true;最后分享一个实际排查中的技巧当遇到性能差异时先简化SQL如去掉排序、减少分组字段逐步定位瓶颈点比直接分析复杂SQL更高效。