Как ускорить выполнение запроса в Postgres?

Question

[AStek]

Есть таблица с целочисленными полями A и B, причем по полю А построен индекс B-дерево. В таблице около 10 милионов записей. Выполняется следующий запрос:

SELECT b FROM table1 WHERE a IN ( ... 1000 значений ... ) ORDER BY a

Запрос выполняется около 100 секунд.
Заказчик жалуется что это долго.
Есть ли у кого идеи как можно ускорить выполнение этого запроса?

Comments

[AStek]

Да, таблицу при этом никто не лочит и не пытается в нее писать. Выполняется все в один поток.

[AStek]

И да, данные выбираются для того чтобы быть закешироваными, так-что кеширование здесь не спасет.

Answer 1

[mOlind]

Есть несколько сторон ускорения работы запроса.
1. оптимизация железа
2. оптимизация базы данных
3. оптимизация самого запроса

1. Посмотреть загрузку жесткого диска во время вы полнения запроса через iostat -x 1
Если %util уперлась в 100% думать над ускорением дисковой подсистемы, возможно ставить raid-0
2. это обширная тема. Если вкратце надо разрешить базе использовать больше памяти во время выполнения запроса. См в сторону: shared_buffers, work_mem, maintenance_work_mem и статей по конфигурации postgresql
3. Смотрим сами индексы и порядок работы с ними. Возможно индексы в столбцах надо поменять с GiST на GIN (www.postgresql.org/docs/9.1/static/textsearch-inde... Возможно надо сделать индексы по нескольким столбцам, чтобы констукция WHERE a AND b работала быстрее.
Еще если во время работы запроса делается одно и тоже действие по преобразованию данных, возможно надо сделать предобработку данных, чтобы это вычисление уже было готово (лежало в отдельном столбце) и потом по нему строить индекс и гонять выборку.

Гоняю запросы по данным мира в таблицах 100 млн записей. Сложные выборки обрабатываются меньше чем за секунду.

Answer 2

[Андрей]

Ну для начала нужно посмотреть план запроса. Сам в свое время решал подобную проблему заменой where .. in на кучу запросов a = ... через union, но в моём случае было не 1000 значений, а несколько меньше.

Comments

[AStek]

Спасибо за ответ. После дополнительных консультаций с коллегами мы пришли к выводу что уперлись в производительность Postgres. В плане присутствует один единственный Index Scan с фильтром ANY для множества идентификаторов. К сожалению из-за NDA не могу привести оригинал а мудрить не хочу. Юнион тут вряд ли будет хорошим решением, уж слишком много данных нужно выбрать.