Улучшение скорости запросов: простой SELECT в большой таблице postgres

У меня возникают проблемы с скоростью в запросе SELECT в базе данных Postgres.

У меня есть таблица с двумя целыми столбцами в качестве ключа: (int1, int2) Эта таблица содержит около 70 миллионов строк.

Мне нужно сделать два простых запроса SELECT в этой среде:

SELECT * FROM table WHERE int1=X;
SELECT * FROM table WHERE int2=X;

Эти два выбора возвращают около 10.000 строк каждый из этих 70 миллионов. Чтобы это работало как можно быстрее, я подумал об использовании двух индексов HASH, по одному для каждого столбца. К сожалению, результаты не так хороши:

                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on lec_sim  (cost=232.21..25054.38 rows=6565 width=36) (actual time=14.759..23339.545 rows=7871 loops=1)
   Recheck Cond: (lec2_id = 11782)
   ->  Bitmap Index Scan on lec_sim_lec2_hash_ind  (cost=0.00..230.56 rows=6565 width=0) (actual time=13.495..13.495 rows=7871 loops=1)
         Index Cond: (lec2_id = 11782)
 Total runtime: 23342.534 ms
(5 rows)

Это пример EXPLAIN ANALYZE одного из этих запросов. Это занимает около 23 секунд. Мои ожидания - получить эту информацию менее чем за секунду.

Это некоторые параметры конфигурации postgres db:

work_mem = 128MB
shared_buffers = 2GB
maintenance_work_mem = 512MB
fsync = off
synchronous_commit = off
effective_cache_size = 4GB

Любая помощь, комментарий или мысль будут действительно оценены.

Спасибо заранее.

Ответ 1

Извлечение моих комментариев в ответ: поиск индекса здесь был очень быстрым - все время было потрачено на извлечение фактических строк. 23 секунды /7871 строк = 2,9 миллисекунды на строку, что разумно для получения данных, которые разбросаны по дисковой подсистеме. Иски медленны; вы можете a) поместить свой набор данных в ОЗУ, b) купить SSD или c) организовать свои данные заблаговременно, чтобы свести к минимуму запросы.

PostgreSQL 9.2 имеет функцию, называемую сканирование только по индексу, которая позволяет ему (обычно) отвечать на запросы без доступа к таблице. Вы можете комбинировать это с свойством индекса btree для автоматического поддержания порядка, чтобы быстро выполнить этот запрос. Вы указываете int1, int2 и два поплавка:

CREATE INDEX sometable_int1_floats_key ON sometable (int1, float1, float2);
CREATE INDEX sometable_int2_floats_key ON sometable (int2, float1, float2);

SELECT float1,float2 FROM sometable WHERE int1=<value>; -- uses int1 index
SELECT float1,float2 FROM sometable WHERE int2=<value>; -- uses int2 index

Обратите также внимание на то, что это не волшебным образом удаляет запросы на диск, а просто перемещает их из времени запроса для вставки времени. Это также стоит вам пространство для хранения, поскольку вы дублируете данные. Тем не менее, это, вероятно, компромисс, который вы хотите.

Ответ 2

Спасибо, ты будешь. Как вы заметили, проблема заключалась в поиске через HD и не поиске индексов. Вы предложили множество решений, таких как загрузка набора данных в оперативной памяти или покупка HD-SSD. Но, забыв об этих двух, которые связаны с управлением вещами вне самой базы данных, вы предложили две идеи:

Реорганизовать данные, чтобы уменьшить поиск данных.
Использовать функцию PostgreSQL 9.2 "только для индексирования"

Поскольку я находится на сервере PostgreSQL 9.1, я решил использовать опцию "1".

Я сделал копию таблицы. Итак, теперь у меня одна и та же таблица с теми же данными дважды. Я создал индекс для каждого из них, первый из которых индексируется (int1), а второй - (int2). Затем я сгруппировал их (таблица CLUSTER USING ind_intX) с помощью соответствующих индексов.

Теперь я отправляю EXPLAIN ANALYZE того же запроса, который выполняется в одной из этих кластерных таблиц:

                                                         QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------------------  
Index Scan using lec_sim_lec2id_ind on lec_sim_lec2id  (cost=0.00..21626.82 rows=6604 width=36) (actual time=0.051..1.500 rows=8119 loops=1)
Index Cond: (lec2_id = 12300)  Total runtime:
1.822 ms (3 rows)

Теперь поиск действительно быстрый. Я снизился с 23 секунд до ~ 2 миллисекунд, что является впечатляющим улучшением. Я думаю, что эта проблема решена для меня, я надеюсь, что это может быть полезно и для других, испытывающих ту же проблему.

Большое вам спасибо.

Ответ 3

У меня был случай сверх медленных запросов, в которых между одним и несколькими соединениями (в PG v9.1) выполнялась таблица, которая составляла 33 миллиона строк для дочерней таблицы, размер которой составлял 2,4 миллиарда строк. Я выполнил CLUSTER по индексу внешнего ключа для дочерней таблицы, но обнаружил, что это не решило мою проблему с таймаутами запросов даже для самых простых запросов. Запуск ANALYZE также не помог решить проблему.

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

То, что я убираю из этого, состоит в том, что регулярные операции VACUUM по-прежнему имеют решающее значение, даже для v9.1. Причина, по которой я делал это, заключалась в том, что я заметил, что autovacuum не работал ни на одном из столов в течение как минимум двух недель, и с тех пор произошло много перегрузок и вставок. Возможно, мне нужно улучшить автовакуумный триггер, чтобы позаботиться об этом вопросе, но я могу сказать, что таблица 640 ГБ с несколькими миллиардами строк работает хорошо, если все очищено. Мне еще не пришлось разбивать таблицу, чтобы получить хорошую производительность.