Because of subquery result cache, the query's result may be old. The data will be cleaned(then will recreate in next query) after lightdb_result_cache_clean_interval seconds.

You must use 'lt_result_cache' hint to use this optimization. The hint only takes effect for the first layer of subqueries in the query statement, and there can only be one subquery in the top-level query block. Here is an example:

create table test_p(key1 int primary key, key2 text);

insert into test_p select s, 'dsdsds' from generate_series(1, 1000000) as s;

create table test_p1(key1 int primary key, key2 text);

insert into test_p1 select s, 'dsdsds' from generate_series(1, 1000000) as s;

select * from 
 ( select/*+lt_result_cache*/  rownum as row_num , a.* from
      (select x.key1 from (select * from test_p order by key2) x, 
            (select * from test_p1 order by key2) x1 where x.key1=x1.key1*2 order by x.key1 
      ) a
 )b where row_num >1 and row_num < 5;

The lightdb_result_cache_clean_interval option determines when to clean the cache.