背景

客户环境中遇到一个慢查询，条件中有 ‘rownum <= 2000’， 一般来讲加了rownum的条件后只会返回少部分行，不应有性能问题，而该查询改为select * 后也证实了这点。那问题在哪儿？

环境模拟

因涉密问题，不好直接用原始语句，我们来模拟下客户场景。

tpch=> explain analyze select count(*) from lineitem where l_tax > 0 and rownum <= 10;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1253523.59..1253523.60 rows=1 width=8) (actual time=61278.833..61278.833 rows=1 loops=1)
   ->  Seq Scan on lineitem  (cost=0.00..1231369.77 rows=8861529 width=0) (actual time=0.009..61278.828 rows=10 loops=1)
         Filter: ((l_tax > 0::numeric) AND (ROWNUM <= 10))
         Rows Removed by Filter: 29999785
 Total runtime: 61278.889 ms
(5 rows)

分析

该语句的执行计划中未发现LIMIT，看上去不正常。我们去掉 COUNT, 改为日常性能无问题的模式来查看：

tpch=> explain analyze select  * from lineitem where l_tax > 0 and rownum <= 10;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.43 rows=10 width=129) (actual time=0.007..0.011 rows=10 loops=1)
   ->  Seq Scan on lineitem  (cost=0.00..1156362.48 rows=26584586 width=129) (actual time=0.007..0.011 rows=10 loops=1)
         Filter: (l_tax > 0::numeric)
         Rows Removed by Filter: 3
 Total runtime: 0.081 ms
(5 rows)

对比非常明显，时间由60多秒遽降到1秒以下。 我们把原语句改写来测试下：

tpch=> explain analyze select count(*) from (select * from lineitem where l_tax > 0 and rownum <= 10);
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=0.56..0.57 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=1)
   ->  Limit  (cost=0.00..0.43 rows=10 width=129) (actual time=0.009..0.013 rows=10 loops=1)
         ->  Seq Scan on lineitem  (cost=0.00..1156362.48 rows=26584586 width=129) (actual time=0.008..0.012 rows=10 loops=1)
               Filter: (l_tax > 0::numeric)
               Rows Removed by Filter: 3
 Total runtime: 0.069 ms
(6 rows)

很明显是rownum的特性在遇到count时失效了。经咨询研发，可以通过设置参数来使使用该特性。

tpch=> explain analyze select count(*) from lineitem where l_tax > 0 and rownum <= 10;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1253523.59..1253523.60 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=1)
   ->  Limit  (cost=0.00..1231369.77 rows=8861529 width=0) (actual time=0.011..0.015 rows=10 loops=1)
         ->  Seq Scan on lineitem  (cost=0.00..1231369.77 rows=8861529 width=0) (actual time=0.010..0.011 rows=10 loops=1)
               Filter: (l_tax > 0::numeric)
               Rows Removed by Filter: 3
 Total runtime: 0.128 ms
(6 rows)

我们也可以把参数加在hint中，有兴趣的朋友也可以不改语句，而使用SQL_PATCH来处理。

tpch=> explain analyze select  /*+ set(rewrite_rule 'rownumenhance') */ count(*) from lineitem where l_tax > 0 and rownum <= 10;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1253523.59..1253523.60 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=1)
   ->  Limit  (cost=0.00..1231369.77 rows=8861529 width=0) (actual time=0.009..0.012 rows=10 loops=1)
         ->  Seq Scan on lineitem  (cost=0.00..1231369.77 rows=8861529 width=0) (actual time=0.008..0.010 rows=10 loops=1)
               Filter: (l_tax > 0::numeric)
               Rows Removed by Filter: 3
 Total runtime: 0.063 ms
(6 rows)

结论

目前默认场景下rownum+聚合函数可能会有性能问题，可以尝试更改语句或更改rewrite_rule的方式处理。