LIMIT and OFFSET are particularly useful clauses in PostgreSQL. LIMIT, as it’s name suggests, limits the number of rows returned in a SELECT query. For instance, if the limit count is 4, maximum of four rows are returned, or less than 4, if the query itself yield less number of rows.
Consider the following table:
CREATE TABLE products ( id integer NOT NULL, name character varying, cost double precision, manufacturer character varying, warranty character varying, category character varying, in_stock boolean, CONSTRAINT products_pkey PRIMARY KEY (id) )
The following query, returns 4 rows:
SELECT * FROM products LIMIT 4 ;
You should never assume the query to return rows in the same order as it appears in the table.
The OFFSET clause is used to skip a fixed number of rows in the result. For instance, OFFSET 5 will, skip the first 5 rows, and return the rest of the rows.
Example:
SELECT * FROM products OFFSET 5;
LIMIT or OFFSET without an ORDER BY clause may return an unpredictable result set. It is always preferred to use ORDER BY clause to make it predictable and consistent across repeated execution of the same query.
An OFFSET of zero is as same as omitting the OFFSET clause.
LIMIT , OFFSET & ORDER BY for Pagination
In most of the real world scenarios, LIMIT and OFFSET together with ORDER BY is used to get the desired result.
As an example the following query returns the products, ordered by category and cost, skipping the first 5 products, limiting the result to 6.
SELECT * FROM products ORDER BY category,cost LIMIT 6 OFFSET 5 ;
This query would remind us the common pagination requirement found in e-commerce websites. The next set of results can be returned by changing the OFFSET value alone.
Remember, the last “page” returned by OFFSET 10, will return only 5 rows. Because our table only contain 15 rows in total.
A little more about the ORDER clause
The ORDER BY clause not always needs to be used along with a LIMIT or OFFSET. It can be used with any SELECT query. Sort has always two options, descending or ascending.
The default is ascending. To make it explicit one may use the ASC keyword:
SELECT * FROM products ORDER BY warranty ASC;
To sort in descending order use the DESC keyword:
SELECT * FROM products ORDER BY warranty DESC;
To learn more about the ORDER BY clause visit this post: [link to be updated]
Performance Considerations
PostgreSQL take into account the LIMIT clause while building the query plan. So, different LIMIT and OFFSET values generate different plans, and the order of returned row may change substantially. Use explicit ORDER BY clause to return a predictable result set.
LIMIT is also useful for DBAs to get some sample rows out of a table with large number of rows. A LIMIT 0 can be used in situations where you just want to know what are the columns available in the table.
A large OFFSET is inherently costly and yield poor performance. This is because the rows skipped still have to be computed internally by the server, even though it is not returned.
paginations_in_postgresql_sample_data_dump.sql
