LIMIT and OFFSET pagination in PostgreSQL - Step-by-Step Execution

Concept Flow - LIMIT and OFFSET pagination

Start Query

↓

Apply ORDER BY

↓

Apply OFFSET

↓

Skip N rows

↓

Apply LIMIT

↓

Return M rows

↓

End Query

The query first orders rows using ORDER BY, then skips a number of rows using OFFSET, then returns a limited number of rows using LIMIT.

Execution Sample

PostgreSQL

SELECT * FROM employees ORDER BY id LIMIT 3 OFFSET 2;

This query returns 3 rows from the employees table, skipping the first 2 rows.

Execution Table

Step	Action	Rows Considered	Rows Skipped (OFFSET)	Rows Returned (LIMIT)	Resulting Rows IDs
1	Start query execution	All rows in employees	0	0	All rows
2	Apply ORDER BY id	All rows sorted by id	0	0	[1, 2, 3, 4, 5, 6]
3	Apply OFFSET 2	Sorted rows	2	0	[3, 4, 5, 6]
4	Apply LIMIT 3	Rows after offset	2	3	[3, 4, 5]
5	Return result	Final rows	2	3	[3, 4, 5]

💡 Query ends after returning 3 rows starting from the 3rd row (offset 2).

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Rows Considered	All rows	Sorted by id	After skipping 2 rows	Limited to 3 rows	Final result set
Resulting Rows IDs	[]	[1, 2, 3, 4, 5, 6]	[3, 4, 5, 6]	[3, 4, 5]	[3, 4, 5]

Key Moments - 2 Insights

Why does OFFSET skip rows before LIMIT is applied?

OFFSET tells the database to skip a number of rows first, then LIMIT restricts how many rows to return from the remaining rows. See execution_table rows 3 and 4.

What happens if OFFSET is larger than the total number of rows?

No rows remain after skipping, so the query returns an empty result. This is because OFFSET skips that many rows first, leaving none to LIMIT. This is implied after step 3 if offset > total rows.

Visual Quiz - 3 Questions

Look at the execution_table, what rows are returned after applying OFFSET 2 and LIMIT 3?

A [1, 2, 3]

B [4, 5, 6]

C [3, 4, 5]

D [2, 3, 4]

💡 Hint

Check rows 3 and 4 in the execution_table for the resulting rows after OFFSET and LIMIT.

At which step does the query skip rows?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look at the 'Rows Skipped (OFFSET)' column in the execution_table.

If LIMIT was changed to 2, how would the final rows change?

A [3, 4]

B [3, 4, 5]

C [2, 3]

D [1, 2]

💡 Hint

Refer to variable_tracker and see how LIMIT controls the number of rows returned after OFFSET.

Concept Snapshot

LIMIT and OFFSET control pagination in SQL.
OFFSET skips a number of rows first.
LIMIT restricts how many rows to return after skipping.
Use ORDER BY to define row order.
Example: SELECT * FROM table ORDER BY id LIMIT 3 OFFSET 2;

Full Transcript

This visual execution shows how LIMIT and OFFSET work together to paginate query results. The query first orders all rows by id. Then OFFSET skips the first 2 rows. After skipping, LIMIT returns the next 3 rows. The execution table tracks each step, showing how rows are filtered. Variable tracking shows the rows considered and resulting rows after each step. Key moments clarify why OFFSET applies before LIMIT and what happens if OFFSET exceeds total rows. The quiz tests understanding of which rows are returned and when skipping happens. The snapshot summarizes the syntax and behavior for quick reference.