I have large tables in Postgres 10.4 (many millions rows) and three piped SELECT queries. The percentage of rows contributed to the total result for each SELECT is roughly:

  • SELECT 1 : ~5 % rows
  • SELECT 2 : ~5 % rows
  • SELECT 3 : ~90 % rows

I can use UNION and ORDER BY and a RANK column, but it's very slow because Postgres collects rows from all subqueries and applies ORDER BY RANK on the result set.

Is there a way to have SELECT 1 executed, and only if that does not provide enough rows SELECT 2 is executed, etc.

  • 2
    Which PG version? Could you post your query? How many tables for those sub-query? You can simulate your structure in this dbfiddle.uk
    – Luan Huynh
    Aug 1, 2018 at 14:17
  • I use the postgresql 10.4. The type of request can will be ``` SELECT * FROM ( (SELECT u.id,1 as rank FROM user u WHERE u.id = 3 UNION SELECT u.id,2 as rank FROM user u WHERE troll = true UNION SELECT u.id,3 as rank FROM user u WHERE country_code = 'FR') ) as u ORDER BY rank OFFSET 0 LIMIT 30 ```
    – Vincent
    Aug 1, 2018 at 15:03
  • I took the liberty to clarify your question. Please edit if I got anything wrong. Aug 1, 2018 at 15:08

1 Answer 1


There is a general technique to achieve this, with UNION ALL and LIMIT:

SELECT 1 ...
SELECT 2 ...
SELECT 3 ...

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.


  • That works perfectly :)
    – Vincent
    Aug 1, 2018 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.