Оптимизировать запрос с помощью OFFSET на большой таблице

Большой OFFSET всегда будет медленным. Postgres должен упорядочить все строки и подсчитать видимые строки до вашего смещения. Чтобы пропустить все предыдущие строки напрямую, вы можете добавить индексированный row_number в таблицу (или создать _ 3_, включая указанный row_number) и работать с WHERE row_number > x вместо OFFSET x.

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

Я предлагаю другой подход для разбивки на страницы:

SELECT *
FROM   big_table
WHERE  (vote, id) > (vote_x, id_x)  -- ROW values
ORDER  BY vote, id  -- needs to be deterministic
LIMIT  n;

Где vote_x и id_x - из последней строки предыдущей страницы (как для DESC, так и для ASC). Или с первого при навигации назад.

Сравнение значений строк поддерживается уже имеющимся у вас индексом - функцией, которая соответствует стандарту ISO SQL, но не каждая СУБД поддерживает ее.

CREATE INDEX vote_order_asc ON big_table (vote, id);

Или по убыванию:

SELECT *
FROM   big_table
WHERE  (vote, id) < (vote_x, id_x)  -- ROW values
ORDER  BY vote DESC, id DESC
LIMIT  n;

Можно использовать тот же индекс.
Я предлагаю вам объявить свои столбцы NOT NULL или познакомиться с конструкцией NULLS FIRST|LAST:

• Сортировка PostgreSQL по возрастанию даты и времени, сначала null?

В частности, обратите внимание на две вещи:

1. Значения ROW в предложении WHERE нельзя заменить разделенными полями-членами. WHERE (vote, id) > (vote_x, id_x) нельзя заменить на:

   WHERE  vote >= vote_x
   AND    id   > id_x

   Это исключит все строки с id <= id_x, в то время как мы хотим сделать это только для того же голосования, а не для следующего. Правильный перевод будет:

   WHERE (vote = vote_x AND id > id_x) OR vote > vote_x
   

   ... который не так хорошо работает с индексами и становится все более сложным для большего количества столбцов.

   Очевидно, было бы просто для одного столбца. Это особый случай, о котором я упоминал в самом начале.

2. Этот метод не работает для смешанных направлений в ORDER BY, например:

   ORDER  BY vote ASC, id DESC
   

   По крайней мере, я не могу придумать общий способ реализовать это так же эффективно. Если хотя бы один из обоих столбцов является числовым типом, вы можете использовать функциональный индекс с инвертированным значением на (vote, (id * -1)) - и использовать то же выражение в ORDER BY:

   ORDER  BY vote ASC, (id * -1) ASC
   

Связанный:

• термин синтаксиса SQL для 'WHERE (col1, col2 ) ‹(Значение1, значение2) '
• Повышение производительности для упорядочивания по столбцам из многих таблиц

В частности, обратите внимание на презентацию Маркуса Винанда, которую я связал с:

Вы пробовали разделить таблицу на части?

Простота управления, улучшенная масштабируемость и доступность, а также уменьшение количества блокировок - распространенные причины для разделения таблиц. Повышение производительности запросов не является причиной для использования секционирования, хотя в некоторых случаях это может быть полезным побочным эффектом. Что касается производительности, важно убедиться, что ваш план внедрения включает анализ производительности запросов. Убедитесь, что ваши индексы продолжают надлежащим образом поддерживать ваши запросы после того, как таблица будет секционирована, и убедитесь, что запросы, использующие кластеризованные и некластеризованные индексы, выигрывают от исключения секций, где это применимо.

http://sqlperformance.com/2013/09/sql-indexes/partitioning-benefits