7.6. LIMIT and OFFSET
After attaching the limit and offset clauses, you can retrieve a subset of the data rows from the results of the original query query:
SELECT select_list from
table_expression
[LIMIT {number | All}] [OFFSET number]
If a limit count is given, then no more rows are returned. (but probably less, because the query itself may generate fewer rows.) ) LIMIT all and omitting the LIMIT clause.
Offset describes how many rows to ignore before starting to return rows. Offset 0 is the same as omitting the offset clause. If both offset and limit appear, the offset row is ignored before the limit line is computed.
If you are using limit, it is a good idea to constrain the result row into a unique order with an ordered by clause. Or you're going to get an unpredictable subset of the rows of the query. -What you want is probably line tenth to 20th, but in what order 10 to 20. The order is not known unless you have declared an ordered by.
The query optimizer takes limit into account when generating a query plan, so if you give limit and offset different things, you are likely to receive different plans (resulting in different row orders). Therefore, using a different Limit/offset value to select a subset of the different query results produces inconsistent results unless you force a predictable sequence by using order. This is not a bug; this is a natural result, because SQL does not promise to send the results of the query in any particular order unless the order is constrained by the ordering.
The rows ignored by the OFFSET clause still need to be evaluated inside the server, so a large offset may not be efficient.