KingbaseES rownum 与 limit 的 执行计划区别

数据准备

--创建基础数据表100W行

create table test07 as
select *
from (select generate_series(1, 1000000) id,
             (random() * 1000)::int      c1k,
             (random() * 100000)::int    c100k,
             (random() * 1000000)::int   c1m)
order by random();

alter table test07 add primary key (id);
create index test07_c1m on test07 (c1m);
create index test07_c1k on test07 (c1k);
create index test07_c100k on test07 (c100k);

测试SQL例子

--使用 c1k between 1 and 1 条件，决定读取行数占比
--使用 ROWNUM <= 10 条件 或者 LIMIT 10，决定输出行数占比
explain analyse
select *
from (select *
      from test07
      where c1k between 1 and 10
      order by id
     ) t
where rownum <= 100
limit 100
;

SQL语句的执行计划

SQL语句，会产生两种执行计划

• 计划一：根据排序列的索引，先排序后过滤

  QUERY PLAN
  -----------------------------------------------
  Limit  (cost=0.42..592.06 rows=100 width=12) (actual time=0.124..9.451 rows=100 loops=1)
    ->  Index Scan using test07_pkey on test07  (cost=0.42..52779.58 rows=8936 width=12) (actual time=0.123..9.438 rows=100 loops=1)
          Filter: ((c1k >= 1) AND (c1k <= 10))
          Rows Removed by Filter: 11258
  Planning Time: 0.192 ms
  Execution Time: 9.478 ms

• 计划二：根据过滤列的索引，先过滤后排序

  QUERY PLAN
  -----------------------------------------------
  Count  (cost=6553.38..6754.44 rows=0 width=12) (actual time=11.042..11.058 rows=100 loops=1)
    Stop Keys: (ROWNUM <= 100)
    ->  Sort  (cost=6553.38..6575.72 rows=8936 width=12) (actual time=11.038..11.046 rows=101 loops=1)
          Sort Key: test07.id
          Sort Method: quicksort  Memory: 855kB
          ->  Bitmap Heap Scan on test07  (cost=192.02..5966.93 rows=8936 width=12) (actual time=3.123..9.473 rows=10031 loops=1)
                Recheck Cond: ((c1k >= 1) AND (c1k <= 10))
                Heap Blocks: exact=4586
                ->  Bitmap Index Scan on test07_c1k  (cost=0.00..189.79 rows=8936 width=0) (actual time=2.033..2.033 rows=10031 loops=1)
                      Index Cond: ((c1k >= 1) AND (c1k <= 10))
  Planning Time: 0.195 ms
  Execution Time: 11.093 ms

  各种情况的rownum与limit的比较

  • ROWNUM

    过滤比例	输出比例	计划模式	执行时长
    < 30%	1 ~ ALL	计划二	0.1ms ~ 130 ms

    explain (analyse,buffers )
    select *
    from (select *
          from test07
          where c1k between 1 and 300
          order by id
         ) t
    where rownum <= 100;
    
    QUERY PLAN
    -----------------------------------------------
    Count  (cost=43557.67..50297.35 rows=0 width=16) (actual time=80.721..80.759 rows=100 loops=1)
      Stop Keys: (ROWNUM <= 100)
      Buffers: shared hit=6262
      ->  Sort  (cost=43557.67..44306.53 rows=299541 width=16) (actual time=80.717..80.744 rows=101 loops=1)
            Sort Key: test07.id
            Sort Method: quicksort  Memory: 26355kB
            Buffers: shared hit=6262
            ->  Bitmap Heap Scan on test07  (cost=6382.72..16310.84 rows=299541 width=16) (actual time=19.502..41.889 rows=300094 loops=1)
                  Recheck Cond: ((c1k >= 1) AND (c1k <= 300))
                  Heap Blocks: exact=5435
                  Buffers: shared hit=6262
                  ->  Bitmap Index Scan on test07_c1k  (cost=0.00..6307.84 rows=299541 width=0) (actual time=19.105..19.105 rows=300094 loops=1)
                        Index Cond: ((c1k >= 1) AND (c1k <= 300))
                        Buffers: shared hit=827
    Planning Time: 0.229 ms
    Execution Time: 80.801 ms

    过滤比例	输出比例	计划模式	执行时长
    < 30%	1 ~ ALL	计划一	0.1 ms ~ 400 ms

    explain (analyse,buffers )
    select /*+ indexscan(test07) */ *
    from (select *
          from test07
          where c1k between 1 and 300
          order by id
         ) t
    where rownum <= 100;
    
    QUERY PLAN
    -----------------------------------------------
    Count  (cost=0.42..58767.29 rows=0 width=16) (actual time=0.031..0.643 rows=100 loops=1)
      Stop Keys: (ROWNUM <= 100)
      Buffers: shared hit=429
      ->  Index Scan using test07_pkey on test07  (cost=0.42..52776.47 rows=299541 width=16) (actual time=0.028..0.611 rows=101 loops=1)
            Filter: ((c1k >= 1) AND (c1k <= 300))
            Rows Removed by Filter: 324
            Buffers: shared hit=429
    Planning Time: 0.261 ms
    Execution Time: 0.681 ms

    过滤比例	输出比例	计划模式	执行时长
    > 30%	1 ~ ALL	计划一	0.1ms ~ 400ms

    explain (analyse,buffers )
    select *
    from (select *
          from test07
        --  where c1m between (1000000*(1-0.320))::int and 1000000
          where c1k between 1 and 400
          order by id
         ) t
    where rownum <= 100;
    
    QUERY PLAN
    -----------------------------------------------
    Count  (cost=0.42..60774.27 rows=0 width=16) (actual time=0.032..0.489 rows=100 loops=1)
      Stop Keys: (ROWNUM <= 100)
      Buffers: shared hit=304
      ->  Index Scan using test07_pkey on test07  (cost=0.42..52776.47 rows=399890 width=16) (actual time=0.029..0.458 rows=101 loops=1)
            Filter: ((c1k >= 1) AND (c1k <= 400))
            Rows Removed by Filter: 200
            Buffers: shared hit=304
    Planning Time: 0.197 ms
    Execution Time: 0.526 ms

  • LIMIT

    过滤比例	输出比例	计划模式	执行时长
    > 5%	< 1%	计划一	< 85 ms
    < 5%	< 1%	计划二	< 25 ms
    > 5%	> 1%	计划二	25 ms ~ 35 ms
    < 5%	> 1%	计划一	25 ms ~ 35 ms

总结

使用ROWNUM 作为输出行数的条件，不考虑 “FIRST_ROWS”的需求，仅考虑过滤条件的索引。

LIMIT更为精细的权衡过滤条件，可以获得更好的执行计划和时长。如果希望从一个大表中，更快的获得“首页”数据，建议使用LIMIT。