如下所示：
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差异不大。并没有可感知的对性能造成影响
