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

Question

У меня есть стол

create table big_table (
id serial primary key,
-- other columns here
vote int
); 

Эта таблица очень большая, примерно 70 миллионов строк, мне нужно запросить:

SELECT * FROM big_table
ORDER BY vote [ASC|DESC], id [ASC|DESC]
OFFSET x LIMIT n  -- I need this for pagination

Как вы знаете, когда x большое количество, такие запросы очень медленные.

Для оптимизации производительности я добавил индексы:

create index vote_order_asc on big_table (vote asc, id asc);

а также

create index vote_order_desc on big_table (vote desc, id desc);

EXPLAIN показывает, что выше SELECT Запрос использует эти индексы, но в любом случае он очень медленный с большим смещением.

Что я могу сделать, чтобы оптимизировать запросы с OFFSET в больших таблицах? Может быть, PostgreSQL 9.5 или даже более новые версии имеют некоторые особенности? Я искал, но ничего не нашел.

Answer 1

Большой OFFSET всегда будет медленным Postgres должен упорядочить все строки и посчитать видимые до вашего смещения. Чтобы пропустить все предыдущие строки напрямую, вы можете добавить индексированный row_number к столу (или создать MATERIALIZED VIEW в том числе сказал 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). Или с первого раза, если навигация задом наперед.

Сравнение значений строк поддерживается уже имеющимся у вас индексом - функцией, которая соответствует ANSI 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 по datetime asc, сначала 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 для "ГДЕ (col1, col2) <(val1, val2)"
• Повысить производительность при заказе с помощью столбцов из многих таблиц

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

• "Разбивка на страницы в PostgreSQL"

Answer 2

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

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

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