Firebird Pagination with FIRST and SKIP: Deterministic Results and Performance Tips

Use FIRST and SKIP with ORDER BY for reliable pagination

To return a specific slice of rows from a Firebird table, combine the FIRST and SKIP clauses with an ORDER BY that defines a deterministic order.

Worked example

SELECT FIRST 10 SKIP 20
       emp_id,
       name,
       hire_date
FROM   employees
ORDER BY hire_date, emp_id;

This query returns rows 21‑30 (assuming rows are numbered starting at 1) ordered by hire date and then by the primary key emp_id. The ORDER BY guarantees that the same set of rows is returned each time the statement is executed.

How the engine processes the clauses

Firebird first evaluates the ORDER BY to produce a sorted result set, then it discards the first SKIP rows and finally returns the next FIRST rows. If ORDER BY is omitted, the engine may return rows in any physical order, making the offset undefined and the pagination non‑deterministic.

Checking the query plan

Run the statement with EXPLAIN PLAN to verify that the optimizer applies the skip before the fetch:

EXPLAIN PLAN FOR
SELECT FIRST 10 SKIP 20
       emp_id,
       name,
       hire_date
FROM   employees
ORDER BY hire_date, emp_id;

Look for a SORT node followed by a SKIP/FIRST operation in the plan output. If you see a full table scan without a sort, the ORDER BY may be missing or the optimizer could not use an index.

Limits and common mistakes

• Missing ORDER BY – leads to unpredictable results; always include a unique sort key (e.g., primary key) after any business‑logic columns.
• Large SKIP values – the engine must read and discard all preceding rows, which can become slow for deep pages. Consider keyset pagination (seek method) when you need to page beyond a few thousand rows.
• Collation sensitivity – text columns ordered with a case‑sensitive collation may produce unexpected ordering; verify the column’s collation or explicitly cast with COLLATE.
• Out‑of‑date statistics – the optimizer may not push the FIRST/SKIP down to an index scan if table statistics are stale; refresh them with SET STATISTICS INDEX or UPDATE STATISTICS.

Practical verification

1. Execute the paginated query twice with the same parameters and compare the result sets; they should be identical.
2. Vary the SKIP value (e.g., 0, 10, 20) and confirm that each successive query returns the next block of rows without overlap or gaps.
3. Use EXPLAIN PLAN as shown above to ensure a sort operation is present.