LightDB rownum使用示例及性能测试

如下所示：

lightdb@oradb=# explain analyze select * from (select a.*,rownum rn from (select * from v,v1 where v.id=v1.id and not exists (select 1 from t where id<>v.id) and v1.id <> '31' order by v1.id) a where rownum<100000+1000) where rn>100000;                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on lt_tmp_alias_0x1842908  (cost=36160.35..36160.38 rows=1 width=16) (actual time=897.915..897.925 rows=0 loops=1)
   Filter: (lt_tmp_alias_0x1842908.rn > 100000)
   ->  Count StopKey  (cost=36160.35..36160.37 rows=1 width=16) (actual time=897.912..897.920 rows=0 loops=1)
         ->  Subquery Scan on a  (cost=36160.35..36160.37 rows=1 width=16) (actual time=897.910..897.918 rows=0 loops=1)
               ->  Sort  (cost=36160.35..36160.36 rows=1 width=8) (actual time=897.909..897.917 rows=0 loops=1)
                     Sort Key: t_1.id
                     Sort Method: quicksort  Memory: 33kB
                     ->  Hash Join  (cost=27209.80..36160.34 rows=1 width=8) (actual time=897.892..897.899 rows=0 loops=1)
                           Hash Cond: (t_2.id = t_1.id)
                           ->  Seq Scan on t t_2  (cost=0.00..7763.40 rows=316569 width=4) (actual time=0.046..0.047 rows=1 loops=1)
                                 Filter: ((id)::numeric <> '31'::numeric)
                           ->  Hash  (cost=27209.79..27209.79 rows=1 width=4) (actual time=897.823..897.828 rows=0 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                 ->  Nested Loop Anti Join  (cost=0.00..27209.79 rows=1 width=4) (actual time=897.821..897.823 rows=0 loops=1)
                                       Join Filter: (t.id <> t_1.id)
                                       Rows Removed by Join Filter: 64
                                       ->  Seq Scan on t t_1  (cost=0.00..6172.60 rows=318160 width=4) (actual time=0.016..124.377 rows=320064 loops=1)
                                       ->  Materialize  (cost=0.00..9006.40 rows=318160 width=4) (actual time=0.000..0.000 rows=1 loops=320064)
                                             ->  Seq Scan on t  (cost=0.00..6172.60 rows=318160 width=4) (actual time=0.016..0.017 rows=2 loops=1)
 Planning Time: 2.249 ms
 Execution Time: 898.344 ms
(21 rows)

再看原生limit offset的执行计划：

lightdb@oradb=# explain analyze select * from v,v1 where v.id=v1.id and not exists (select 1 from t where id<>v.id) and v1.id <> '31' order by v1.id limit 1000 offset 100000;
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=36160.36..36160.36 rows=1 width=8) (actual time=967.018..967.027 rows=0 loops=1)
   ->  Sort  (cost=36160.35..36160.36 rows=1 width=8) (actual time=966.998..967.006 rows=0 loops=1)
         Sort Key: t_1.id
         Sort Method: quicksort  Memory: 33kB
         ->  Hash Join  (cost=27209.80..36160.34 rows=1 width=8) (actual time=966.978..966.986 rows=0 loops=1)
               Hash Cond: (t_2.id = t_1.id)
               ->  Seq Scan on t t_2  (cost=0.00..7763.40 rows=316569 width=4) (actual time=0.049..0.050 rows=1 loops=1)
                     Filter: ((id)::numeric <> '31'::numeric)
               ->  Hash  (cost=27209.79..27209.79 rows=1 width=4) (actual time=966.881..966.886 rows=0 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 8kB
                     ->  Nested Loop Anti Join  (cost=0.00..27209.79 rows=1 width=4) (actual time=966.879..966.880 rows=0 loops=1)
                           Join Filter: (t.id <> t_1.id)
                           Rows Removed by Join Filter: 64
                           ->  Seq Scan on t t_1  (cost=0.00..6172.60 rows=318160 width=4) (actual time=0.018..133.848 rows=320064 loops=1)
                           ->  Materialize  (cost=0.00..9006.40 rows=318160 width=4) (actual time=0.000..0.000 rows=1 loops=320064)
                                 ->  Seq Scan on t  (cost=0.00..6172.60 rows=318160 width=4) (actual time=0.016..0.017 rows=2 loops=1)
 Planning Time: 2.011 ms
 Execution Time: 967.468 ms
(18 rows)

从上可知，lightdb的rownum实现和pg原生的limit offset差异不大。并没有可感知的对性能造成影响。