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

Question

[Виктор]

Есть таблица с пятью полями типа int. В ней несколько миллионов записей (в дальнейшем будут десятки миллионов или сотни)
Один из столбцов (word_id) проиндексирован b-tree индексом, по нему происходит поиск вида:

select * from keypoints where word_id in ( около 500-1000 значений)

Такой запрос на моем неспешном ноуте занимает 200-300с, iotop показывает, что идет чтение с диска. В результате может вернуться десятки тысяч записей. Повторно запрос выполняется много быстрее.

В конфиге postgresql менял настройки только авторизации.

Вопрос: такой запрос впринципе некорректен и не может выполняться быстро или можно что-то соптимизировать?

Update:
Вывод explain для одного where:

explain select * from keypoints where word_id=27601;

Bitmap Heap Scan on keypoints (cost=28.37..4118.23 rows=1080 width=24)
Recheck Cond: (word_id = 27601)
-> Bitmap Index Scan on keypoints_search_idx (cost=0.00..28.10 rows=1080 width=0)
Index Cond: (word_id = 27601)
(4 rows)

Time: 696,716 ms

Answer 1

[Melkij]

По порядку:

Повторно запрос выполняется много быстрее.

Значит, вы с головой упёрлись в диск.
Вариант есть только один - ставить быстрый диск.
Вариант похуже, т.к. диск всё равно у вас останется медленный и если не на чтении, то на старте и на записи вы туда упрётесь очень хорошо - использовать больше памяти. Чтобы как можно данных было в памяти. Но при работе эти данные всё равно надо поднимать с медленных дисков, потому базу сначала придётся разогревать. Есть даже штатная утилитка pg_prewarm

В конфиге postgresql менял настройки только авторизации.

По дефолту там что-то несмешное вместо shared_buffers. 32 что ли мегабайта? И это на ключевую характеристику базы.
Увеличивать хотя бы до 20% от общего объёма памяти на машине.

В третьих, актуальный Postgresql довольно плохо умеет развесистый IN. И тому есть не совсем очевидное решение - переписать в join:

select count(*) from test JOIN (VALUES (1),...,(10000)) AS v(val) USING (val);

Такой вот hash join работает быстрее IN. На 10000 элементах получали 10мс против 380мс у IN.

Answer 2

[Макс]

думаю, пора открывать для себя команду EXPLAIN и посмотреть план исполнения, используются ли индексы вообще. Причем смотрите как на основной запрос, так и на вложенный.
судя по

занимает 200-300с, iotop показывает, что идет чтение с диска.

- нет, происходит FULL SCAN.
правка конфига поможет, если захотите поиграться с размерами кэша. Пока проблема, похоже, именно в индексах.

Comments

[Виктор]

Долбавил вывод explain

[Макс]

Виктор: а что у нас в "in ( около 500-1000 значений)" ? там же тоже какой-то SELECT, я предполагаю? сделайте EXPLAIN всего запроса. "Внешний" SELECT ищет по индексу - и ищет не быстро.
4 строки по индексу за 0,6 секунды - это долго. IMHO должно быть раз в 20 быстрее. Совсем древний ноут? винтажный - ламповый?

[Виктор]

500-1000 значений генерятся программно, не используя бд, ноут не оч древний, но может свопит что, для чистоты залью на do, посмотрю там.

[Макс]

Виктор: о как....
тогда может быть имеет смысл сперва залить эти "500-1000" значений в БД в темповую табличку (если бы Вы использовали MySQL, я бы еще посоветовал заюзать движок MEMORY ), и уже "там" (в БД) заварить всю кухню? не удивлюсь, если тормоза именно из-за большого размера IN ( ), который БД еще и не может оптимизировать - он указан в явном виде в параметрах запроса.

[Макс]

Виктор: к слову - оказывается это известный трабл и я "изобрел велосипед" - www.xaprb.com/blog/2006/06/28/why-large-in-clauses...
Так что
1. заливаем список в БД
2. И SELECT c JOIN.
и будет Вам если не счастье, то хотя-бы облегчение.

[Макс]

Виктор: советуют еще вариант - заменить "IN" на "= ANY". Вроде как во втором варианте POSTGRESQL сам создает временную таблицу со списком из "( )" , джойнит - и т.д.

[Виктор]

Я ошибся с количеством записей. Как раз когда их было 2-3 млн, поиск занимал 1с, что мен я устраивало. Теперь, когда их 20-30 млн, поиск идет кучу времени. Я понимаю, что весь объем базы не загрузить в оперативную, но как посмотреть влезает ли туда, хотя бы индекс?

Answer 3

[Дмитрий Ковальский]

А как определяется список "около 500-1000 значений"? Я бы постарался избавиться от вложенного запроса. Через Join или некое WHERE.

Comments

[Виктор]

Программно, не задействуя бд.