Optimze performance for LIMIT / OFFEST on collected result of multiple queries
There is a general technique to achieve this, with UNION ALL and LIMIT:

SELECT 1 ...
UNION ALL
SELECT 2 ...
UNION ALL
SELECT 3 ...
LIMIT N;


Postgres evaluates nested SELECTs in order and stops as soon as enough rows have been returned. The rest is never executed.

This optimization does not happen with an outer ORDER BY, which forces Postgres to collect all candidate rows and sort before applying the LIMIT. Nor does it work for UNION (instead of UNION ALL) which also considers all rows before removing duplicates and, finally, the LIMIT.

You need parentheses around each nested SELECT that has ORDER BY or LIMIT in addition to the outer LIMIT.

Related:


How to force order of WHERE clauses in PostgreSQL?
Optimize a query on two big tables
How to give precedence to rows from one table over matching rows in another?
Constraint exclusion to fetch latest N rows