Как ускорить Postgresql Index Scan?

Question

[TestVDK]

Здравствуйте.

Имеется таблица в 400кк записей, содержит поле integer, есть индекс по этому полю
Нужно выполнить запрос select * from table where field=value.
Вот план запроса:

Index Scan using index_table_on_field on table  (cost=0.57..19452.86 rows=6304 width=58) (actual time=261.061..2025.559 rows=332 loops=1)
   Index Cond: (value = 12345678)
 Total runtime: 2025.644 ms
(3 rows)

При повторном выполнении запрос уже находится в shared_buffers и выполняется гораздо быстрее (несколько мс).

Как ускорить первоначальное выполнение запроса с произвольным value, чтобы данные для любого значения были мгновенно найдены?

Почему такое большое значение actual time в плане запроса?

Answer 1

[Alexey Lesovsky]

выдача результата при первом запросе упирается в скорость чтения c диска, размер индекса и т.н index bloat.
покажите (из psql):
1) \di+ index_table_on_field
2) select * from pgstattuple('index_table_on_field')
3) и вкратце пару слов о дисковой подсистеме (наличие контроллера?, тип дисков, средняя нагрузка по sar -d)

Answer 2

[Timosha]

1) Вам точно всегда нужны все строки запроса? Используйте сортировку и limit если нет
2) Вам точно нужны все поля из таблицы? Если нет, то есть смысл указать необходимые поля после select
3) Если postgresql >= 9.2 и нужно только одно поле из таблицы можно попробовать добавить его в индекс и добиться выполнения IndexOnlyScan
4) Если нет других запросов, или они не критичны, можно кластеризовать (CLUSTER) таблицу по этому индексу
5) ну и нет ничего зазорного в том чтобы первый запрос выполнялся не очень быстро, оптимизируйте размер таблицы и shared_buffers чтобы постараться уложить её в память.

Answer 3

[TestVDK]

Здравствуйте, извините за задержку:
lesovsky:
1) Индексы по искомому полю есть, сейчас не могу показать - пересоздаю индекс после vacuum full
2) ошибку выдает такая команда((
3) Такие диски: www.hetzner.de/en/hosting/produkte_rootserver/ex60 - по поводу sar - не умею им пользоваться, сейчас не установлено

Timosha:
1) Строки я итак беру не все, использую сначала limit 5, потом постранично limit 15 offset ...
2) Даже при выборе select field from table where field=12345678, присутствует Index Only Scan вместо Scan - но все равно поиск долгий, и близко не мгновенный
3) См пункт 2.
4) Буду смотреть документацию по CLUSTER. Есть еще одно поле в таблице аналогичное искомому, с ним ситуация похожая (также нужна выборка по этому полю). Если не затруднит опишите вкратце преимущества CLUSTER.
5) К сожалению таблица растет постоянно, имеется до 10 миллионов уникальных значений field и строки для каждого field непрерывно увеличивается, хочется выдавать пользователям моментально информацию по любому значению, ведь для этого я индекс создавал!

P.S. shared_buffers сколько ставить, у меня сейчас несколько GB (не помню сколько точно), а всего памяти на машине 48GB

P.P.S я месяц держал таблицу, постепенно увеличивал объем, REINDEX и VACUUM вообще не делал
Данные равномерно прибывают и вообще не удаляются (99% INSERT 1% UPDATE)

Буду рад любым рекомендациям и направлениям развития)

ОБНОВЛЕНО
Я сделал VACUUM FULL и DROP/CREATE INDEX - теперь доступ к произвольному значению player_id занимает не больше 2 секунд, но это все равно много. Повторный доступ к player_id, запрашиваемому ранее занимает уже 0.5мс (сидит в shared_buffers).

Timosha:
1) Я немного неправильно написал. У меня в таблице 400кк строк. У них около 10кк разных значений поля player_id. Я делаю запросы вида "select * from table where player_id=123456789 limit 50 offset 150". Здесь основная нагрузка идет на условие where потому при фильтре условием where player_id=123456789 остается максимум 5-10к строк.
2) Основная проблемная таблица participates. Relation_size - 36gb, Total_relation_size - 71gb, Имеется 4 btree индекса по Integer колонкам. Все колонки в таблице - integer 2-4 бита (по необходимости).
4) Следующим шагом проведу кластеризацию
5) Увеличил до 16gb - проверю после перезагрузки

Timosha:
Сначала попробую кластеризацию а потом вариант с частичным индексом

lesovsky:
размеры индексов в предыдущем блоке
с оффсетами проблем нет
ранее с этим сталкивался - сделал промежуточную таблицу с результатами order через row_select - получились целые номера - позиции строк и теперь вместо limit 150 offset 1500 делают where between 1500 and 1650 - очень помогло

Comments

[Timosha]

1) чтобы взять строчки с 100 по 115 первые 100 базе всё равно надо выбрать, а в результатах отбросить, так что при offset'е вы выбираете offset+limit строк
2) размеры таблицы и индексов в мегабайтах укажите плз
4) оно просто организует записи в таблице в том порядке в котором они идут в индексе, при поиске по индексу будет меньше random read
5) уложите таблицу в shared_buffers, ибо винты не быстрые. размер shared_buffers обычно указывают как 1/3 либо 2/3 RAM. В зависимости от необходимости.

[Timosha]

таблицу следовало создать с некоторым fillfactor отличным от 100, например 80 - 85. и индекс тоже, кстати. reindex и vacuum обычно не нужны при включенном autovacuum

[Alexey Lesovsky]

диски SATA это самый начальный уровень, для базы более менее предпочтительно SAS. плюс там SATA-2 (3Gb/s) при большом потоке одновременных запросов к диску можете упереться в потолок производительности.
Покажите размеры индексов.
Если у вас там большие офсеты попробуйте оптимизировать как описано здесь - astarsan.livejournal.com/20121.html

[TestVDK]

Кластеризацию сделал. Первые запросы (не по кэшу) могут занимать больше положенных 20-30мс (до десяти секунд). Но в целом прогресс есть. Сейчас пробую скопировать таблицу и тестировать на копии, чтобы не останавливать приложение. Что еще попробовать для мгновенного доступа к произвольным данным?

[Timosha]

можно попробовать принудительно поднимать таблицу в shared_buffers или в файловый кэш ОС, коли уж так важно время первого обращения.