Как ускорить запрос с DISTINCT в PostgreSQL?

Question

[nurzhannogerbek]

Здравствуйте, товарищи! Помогите пожалуйста разобраться.

Есть довольно простой скрипт. Выглядит он следующим образом:

SELECT DISTINCT("CITY" || ' | '  || "AREA" || ' | ' || "REGION") AS LOCATION FROM youtube

Таблица youtube, которая используется в запросе состоит из 25 миллионов записей. Каждые 5 минут таблица пополняется новыми данными. Данный скрипт отрабатывается очень долго (~50 секунд).

EXPLAIN ANALYZE возвращает следующее:


Я пытаюсь ускорить запрос. Добавил индекс следующим образом:

CREATE INDEX location_index ON youtube ("CITY", "AREA", "REGION")

После добавление индекса запрос не ускорился. Возможно в самом запросе нужно явно указать, чтобы использовался индекс. Как это сделать в PostgreSQL? Знаю, что к примеру в MySQL для этого используется FORCE INDEX. Есть ли вообще смысл в индексе или партиционирование раз в запросе не используется WHERE.

Answer 1

[Melkij]

Планировщик Postgresql на данный момент не умеет loose index scan. Поэтому планировщик вполне обоснованно со своей точки зрения выбирает не использовать какие-либо индексы.

Поэтому если необходим именно distinct по не очень селективному полю - то эффективнее объяснить планировщику что от него хотят руками: https://wiki.postgresql.org/wiki/Loose_indexscan

Вам же скорее необходимо менять схему хранения данных.

Возможно в самом запросе нужно явно указать, чтобы использовался индекс. Как это сделать в PostgreSQL?Возможно в самом запросе нужно явно указать, чтобы использовался индекс. Как это сделать в PostgreSQL?

Никак. Сообщество сознательно PostgreSQL отказывается добавлять какие-либо хинты планировщика, предпочитая узнавать об ошибках планировщика как о багах и по возможности их исправлять.

Comments

[nurzhannogerbek]

Здравствуйте! Спасибо за информацию! =)

Для меня не совсем было понятно работа планировщика PostgreSQL 11. Как я понял из ваших слов явно использовать определенный индекс в запросе не получится и планировщик сам решает что ему использовать. Это немного шокирует. Я думал по крайней мере можно узнать какой индекс используется в запросе, как в таком случаи анализировать запрос?

Ребята выше советуют отказаться от конкатенации. Также из ваших слов я понял, что distinct утяжеляет запрос в плане быстродействия, я правильно понимаю?

Я тут протестил два запроса и они возвращают очень интересные результаты. Запрос без distinct работает быстрее. Ниже приведу результаты теста. Что думаете про это?

1 запрос:

SELECT "CITY", "AREA", "REGION"
FROM youtube
GROUP BY "CITY", "AREA", "REGION"

EXPLAIN ANALYZE на 1 запрос возвращает:
2 запрос:

SELECT DISTINCT "CITY", "AREA", "REGION" FROM youtube

EXPLAIN ANALYZE на 2 запрос возвращает:

[Melkij]

Как я понял из ваших слов явно использовать определенный индекс в запросе не получится и планировщик сам решает что ему использовать.

Совершенно верно

по крайней мере можно узнать какой индекс используется в запросе

explain показывает что по поводу запроса думает планировщик и что тот передаст на вход executor'у
explain analyze показывает, как реально был выполнен запрос.
А explain (analyze, buffers) да на включенном track_io_timing ещё покажет сколько блоков трогали и как долго ждали диски.

Ребята выше советуют отказаться от конкатенации.

Какие-то копейки даст, глобально - всё равно группировка всей большой таблицы быстрой быть не может.

Запрос без distinct работает быстрее.

set max_parallel_workers_per_gather to 0 и будут равны на уровне погрешности. Потому что это один и тот же запрос с точки зрения реализации.
Просто на один запуск нашлось несколько штук свободных воркеров для активации параллельного исполнения запроса. Запрос считали в 3 ядра - вот и получили execution time в 3 раза меньше.

[nurzhannogerbek]

Melkij, вот оно как. Насколько я знаю, PostgreSQL может выполнять запросы в нескольких потоках, используя несколько ядер. Нужно почитать и изучить возможность апгрейда базы данных. С чего следует начать изучение данного вопроса на ваш взгляд?

[Melkij]

Настроить под своё железо max_parallel_workers_per_gather, max_worker_processes, max_parallel_workers
https://www.postgresql.org/docs/current/runtime-co...

[nurzhannogerbek]

Melkij, Спасибо за ссылку! :) Изучу на выходных.

Answer 2

[Максим Y]

Должно помочь избавление от конкатенации.

SELECT DISTINCT CITY, AREA, REGION FROM youtube
или

SELECT CITY,  AREA, REGION
FROM youtube
GROUP BY CITY,  AREA, REGION

Если сильно нужно получать именно конкатенированный результат, то подзапрос все-равно будет быстрее

SELECT  "CITY" || ' | '  || "AREA" || ' | ' || "REGION" FROM (
    SELECT CITY,  AREA, REGION
    FROM youtube
    GROUP BY CITY,  AREA, REGION) t

Comments

[nurzhannogerbek]

Здравствуйте, Максим! Ок, нужно избавиться от конкатенации, я это понял.

Использую ваш первый скрипт:

SELECT "CITY",  "AREA", "REGION"
FROM youtube
GROUP BY "CITY", "AREA", "REGION"

EXPLAIN ANALYZE возвращает:


Как понять используется ли вообще какой либо индекс в запросе? Такое ощущение, что индексирование и не используется планировщиком PostgreSQL 11.

[Melkij]

Как понять используется ли вообще какой либо индекс в запросе?

parallel seq scan.
Читали последовательно в несколько независимых потоков без использования индексов.

Такое ощущение, что индексирование и не используется планировщиком PostgreSQL 11.

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

[nurzhannogerbek]

Melkij, спасибо за объяснения! =) В зависимости от цены, планировщик выбирает путь с наименьшей ценой. Я правильно ли понимаю, что индексирование целесообразнее делать для небольших таблиц? Просто подумал, вел ли себя планировщик также, если объем данных в таблице не был бы таким большим. Как думаете?

[Melkij]

Индексы целесообразны селективные. Если читать надо половину таблицы (или всё таблицу как здесь) - то это дешевле сделать последовательным перебором.
Если надо прочитать 10 строк из миллиарда - индекс нужен.

[Максим Y]

Да, индекс не используется. Мой внутренний планировщик, к сожалению, сильно искажен ораклом и экзадатой. Так что приходится себя заставлять представить: 1) как здесь можно использовать индекс вообще, 2) ну что такое 6 секунд.

В общем случае, как выше сказал Melkij , выбор оптимизатором использования индекса зависит от селективности выборки, а не от размера таблицы. Даже наоборот, индекс на маленькой таблице часто игнорируется, т.к. прочитать индекс, найти адреса нужных строк, прочитать строки в таблице может быть дольше, чем просто прочитать всю таблицу.

Можете еще попробовать такой извращенный сценарий (на тестовой БД):

CREATE INDEX location_index ON youtube ("CITY", "AREA", "REGION");

SET enable_seqscan TO false;

SELECT "CITY",  "AREA", "REGION"
FROM youtube
GROUP BY "CITY", "AREA", "REGION";

и посмотреть, что покажет план. Вероятно пойдет по индексу.

Но, если прирост скорости будет процентов 10, - городить индекс не стоит. Его еще хранить надо и терять скорость вставки данных из-за него.

UPD: пожалуй, выключать enable_seqscan в любом случае стоит только из интереса и экспериментов.

[nurzhannogerbek]

Melkij, Cпасибо! Теперь понятно с индексами. А есть ли смысл в партиционировании, если я указываю искать конкретные данные в большом объеме данных. Предположим из 25 миллионов которые есть в таблице, 5 миллионов соответствуют этому условию:

SELECT "CITY", "AREA", "REGION"
FROM youtube
WHERE
    "CITY"="London"
AND
    "AREA"="Brent"
AND
    "REGION"="WEMBLEY"

[nurzhannogerbek]

Максим Sh, Максим, спасибо за дополнительную информацию!

Поэкспериментировал. Установил: SET enable_seqscan TO false;

Сейчас наглядно вижу, что используется мой индекс (INDEX Cond), но запрос выполняется дольше.

SELECT "CITY", "AREA", "REGION"
FROM youtube
WHERE
    "CITY" IS NOT NULL
AND
    "AREA" IS NOT NULL
AND
    "REGION" IS NOT NULL
GROUP BY "CITY", "AREA", "REGION"

EXPLAIN ANALYZE возвращает:

[Melkij]

UPD: пожалуй, выключать enable_seqscan в любом случае стоит только из интереса и экспериментов.

Только для экспериментов, да. Как и другие enable_* крутилки планировщика.
Кстати, seqscan на самом деле выключить нельзя, off просто задирает планировщику стоимость seqscan в небо.

Касательно партицирования - это в первую очередь инструмент для управления и архивации данных, а не производительности. Когда вместо долгого delete просто и быстро отстёгивается раздел и уносится в архив, а при необходимости точно так же обратно пристёгивается обратно.
Хотя для производительности тоже использовать можно, и в эту сторону есть порядком улучшений в последние года. Для всего лишь 25 млн впрочем вряд ли имеет смысл.

Предположим из 25 миллионов которые есть в таблице, 5 миллионов соответствуют этому условию:

А далее следует классический вопрос DBA: а нафига вам на приложении понадобилось разом 5 млн строк? Что вы с ними делать будете? Пользователю вывалите?

[nurzhannogerbek]

Melkij, это я так, для примера такие цифры привел. В любом случаи, спасибо, что на пальцах все объяснили!

Answer 3

[Сергей Горностаев]

Составной индекс по полям - это не то же самое, что индекс по конкатенированному значению этих полей. Соответствующий индекс должен создаваться так

CREATE INDEX youtube_location_idx ON youtube ((CITY || ' | '  || AREA || ' | ' || REGION));

Но ещё лучше пересмотреть подход к структуре таблицы и запросов.

Comments

[nurzhannogerbek]

Сергей, здравствуйте!

Создал индекс как у вас на примере. Скорость запроса не изменилась к сожалению. Такое ощущение как будто индекс в запросе и не используется.

Пробовал также такой запрос:

SELECT CONCAT("CITY", ' | ', "AREA", ' | ', "REGION") AS LOCATION FROM (
	SELECT "CITY", "AREA", "REGION", COUNT(*) FROM youtube GROUP BY "CITY", "AREA", "REGION"
) youtube;

Выполняется он быстрее чем первый, но все равно медленно:


Как думаете есть ли смысл в индексировании и партиционировании, ведь в запросе нет условия WHERE?

[Сергей Горностаев]

NogerbekNurzhan, я думаю, что использование функциональных индексов и операций над строками где-либо, кроме вывода - это признаки непонимания реляционных БД. В таблице youtube должно быть поле city, являющееся внешним ключом на одноимённую таблицу, в которой в свою очередь должно быть поле area, ссылающееся на таблицу area, и так далее.

[Hedy]

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

[nurzhannogerbek]

Сергей, согласен с вами. Каждое поле (CITY, AREA, REGION) нужно разбить по отдельным таблицам. Администрированием базы данных я не занимаюсь. Я лишь веб-разработчик, который использует уже готовую таблицу для своих нужд. Приходится выкручиваться.

[nurzhannogerbek]

Pavel Denisov, спасибо за совет! Решил отказаться от конкатенации в запросе. Объединять значения буду на стороне клиента.