Summary of 2 ways to compare the time of PostgreSQL pagination query

3000W pieces of data are stored in the database, and the two types of paging query test time are

The first type

SELECT * FROM test_table WHERE i_id> 1000 limit 100;

Time: 0.016s

The second type

SELECT * FROM test_table limit 100 OFFSET 1000;

Time: 0.003s

The first type

SELECT * FROM test_table WHERE i_id> 10000 limit 100;

Time: 0.004s

The second type

SELECT * FROM test_table limit 100 OFFSET 10000;

Time: 0.508s

The first type:

SELECT * FROM test_table WHERE i_id> 100000 limit 100;

Time: 0.003s

The second type:

SELECT * FROM test_table limit 100 OFFSET 100000;

Time: 2.377s
SELECT * FROM test_table WHERE c_act='Log in' limit 100 OFFSET 100000;

Time: 3.649s

The first type:

SELECT * FROM test_table WHERE i_id> 1000000 limit 100;

Time: 0.004s

The second type:

SELECT * FROM test_table limit 100 OFFSET 1000000;

Time: 14.403s

The first type:

SELECT * FROM test_table WHERE i_id> 10000000 limit 100;

Time: 0.057s

The second type:

Losing patience to wait for the results to come out! ! !

It is recommended to use the first pagination method~

Supplement: postgreSQL database limit pagination and sorting

limit pagination syntax:

select * from persons limit A offset B;

explain:

A is how many rows you need to display;

B is the starting point of the query.

Example:

select * from persons limit 5 offset 0 ;

It means that starting point 0 starts query and returns 5 pieces of data.

select * from persons limit 15 offset 5 ;

It means that starting point 5 starts querying and returns 15 pieces of data.

special:

select * from persons limit 5 ;

This is similar to:

select * from persons limit 5 offset 0;

That is, start the query from the starting point 0 and return 5 pieces of data.

Sort by rules, and also paginate:

select * from persons order by lastname limit 5 offset 0;

Paginate and display line numbers, similar to rownum in oracle:

select *,row_number() over() as rownum from persons limit 5 offset 0;

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.