Выполнить второй SQL-запрос, если первый возвращает меньше строк, чем ограничение

Один из трюков, который вы можете использовать, — это добавить вычисляемый столбец для отслеживания каждой половины объединения. Затем снова выполните подзапрос и ограничите порядок, используя этот вычисляемый столбец для обеспечения порядка:

SELECT *
FROM
(
    (
        SELECT *, 1 AS src
        FROM subscriptions
        WHERE (last_sync_at IS NULL OR last_sync_at < updated_at) AND
          updated_at < $1
        ORDER BY last_sync_at NULLS FIRST
        LIMIT $2
    )
    UNION
    (
        SELECT *, 2
        FROM subscriptions
        ORDER BY last_sync_at NULLS FIRST
        LIMIT $1
    )
) t
ORDER BY src
LIMIT 100;

В приведенном выше примере будут использоваться записи из первого запроса по приоритету для заполнения предельной квоты в 100. Только если этот первый запрос возвращает менее 100 записей, будут использоваться записи из второго запроса.

Я нашел правильный ответ, немного изменив свой первый подход. Я по-прежнему использую UNION и COUNT, но на этот раз я упростил сложность, используя предложение С:

WITH unsynced_or_recently_updated_subscriptions AS (
    SELECT *
    FROM subscriptions
    WHERE (last_sync_at IS NULL OR last_sync_at < updated_at) 
    AND updated_at < $1
    ORDER BY last_sync_at NULLS FIRST
    LIMIT $2
),
oldest_synced_subscriptions AS (
    SELECT *
    FROM subscriptions
    WHERE id NOT IN (SELECT id FROM unsynced_or_recently_updated_subscriptions)
    ORDER BY last_sync_at NULLS FIRST
    LIMIT $2 - (SELECT COUNT(*) FROM unsynced_or_recently_updated_subscriptions)
)
SELECT *
FROM unsynced_or_recently_updated_subscriptions
UNION ALL
SELECT *
FROM oldest_synced_subscriptions;

Объяснение:

При таком подходе второй запрос ограничивается [the number of rows of the first query] - [the limit]. Что очень эффективно. Потому что, если первый запрос превысил лимит, второй запрос будет иметь лимит 0 и немедленно вернет результат.

Вопросы производительности:

Насколько я понимаю, этот комбинированный подход обычно более эффективен, чем выполнение двух отдельных запросов, поскольку он снижает накладные расходы, связанные с несколькими подключениями к базе данных.

Единственный сценарий, в котором два отдельных запроса могут быть более производительными, — это когда первый запрос последовательно удовлетворяет пределу, и в этом случае второй запрос не нужен. Однако в моем случае такая ситуация вряд ли будет часто возникать.

Кроме того, предложение NOT IN должно оказывать минимальное влияние на производительность, поскольку столбец id индексируется.

Обновлено:

Еще лучшим решением может быть использование NOT EXISTS вместо NOT IN для второго подзапроса:

oldest_synced_subscriptions AS (
    SELECT *
    FROM subscriptions
    WHERE NOT EXISTS (
        SELECT id FROM unsynced_or_recently_updated_subscriptions 
        WHERE unsynced_or_recently_updated_subscriptions.id = subscriptions.id
    )
    ORDER BY last_chartmogul_sync_at NULLS FIRST
    LIMIT $2 - (SELECT COUNT(*) FROM unsynced_or_recently_updated_subscriptions)
)

Предложение NOT EXISTS обычно более эффективно, чем NOT IN, особенно при работе со значениями NULL. С точки зрения производительности NOT EXISTS часто работает быстрее, чем NOT IN, потому что он может прекратить оценку, как только найдет совпадение, тогда как NOT IN, возможно, придется сканировать весь набор результатов подзапроса.