#sql #postgresql #postgresql-9.5
#sql #postgresql #postgresql-9.5
Вопрос:
В моей базе данных Postgresql 9.5 есть таблица с именем events. И в этой таблице около 6 миллионов записей.
Я выполняю select count(event_id) from events запрос. Но этот запрос занимает 40 секунд. Для базы данных это очень долгое время. Мое event_id поле таблицы является первичным ключом и индексируется. Почему это занимает очень много времени? (Сервер — виртуальная машина Ubuntu на vmware с 4cpu)
Объясните:
"Aggregate (cost=826305.19..826305.20 rows=1 width=0) (actual time=24739.306..24739.306 rows=1 loops=1)"
" Buffers: shared hit=13 read=757739 dirtied=53 written=48"
" -> Seq Scan on event_source (cost=0.00..812594.55 rows=5484255 width=0) (actual time=0.014..24087.050 rows=6320689 loops=1)"
" Buffers: shared hit=13 read=757739 dirtied=53 written=48"
"Planning time: 0.369 ms"
"Execution time: 24739.364 ms"
Комментарии:
1. Отредактированный пост объясняет.
2. 757739 блоков всего на 6 миллионов строк кажется довольно большим. Это примерно 8 строк на блок. Сколько столбцов в таблице? Или в нем есть несколько длинных текстовых столбцов (в них менее 2 кб). Также может быть раздувание таблицы. Изменится ли это, если вы очистите таблицу с помощью
vacuum full events;?3. В таблице 7 столбцов. Но в одном столбце есть
textтип и такие длинные данные json.
Ответ №1:
Я знаю, что это старый вопрос, и существующий ответ охватывает подавляющее большинство информации по этому вопросу, но я только что столкнулся с ситуацией, когда для выполнения простой таблицы из 1,3 миллиона строк требовалось около 35 секунд SELECT COUNT(*) . Ни одно из других решений не помогло. Проблема заключалась в том, что таблица была просто раздута и не была очищена, поэтому Postgres не смогла определить наиболее оптимальный способ запроса данных. После того, как я запустил это, время запроса сократилось примерно до 25 мс!
VACUUM (ANALYZE, VERBOSE, FULL) my_table_name;
Надеюсь, это поможет кому-то еще!
Комментарии:
1. Алек, ты мой спаситель! Я потратил часы, пытаясь выяснить, почему выполнение моих запросов COUNT занимало ~ 8 минут. Оказывается, функции вакуумирования Postgres по умолчанию было недостаточно, и требовался ручной вакуум.
2. Рад помочь 🙂
3. @AlecSanger Сколько времени занимает эта команда ВАКУУМИРОВАНИЯ? У меня в таблице три миллиона записей. Запрос выполняется в фоновом режиме, поэтому я не могу проверить.
4. Вакуум может занять некоторое время в зависимости от того, сколько там данных и в каком состоянии они находятся. В прошлом у меня это занимало что-то около 10 минут. Однако при следующем запуске он должен быть очень быстрым, что подтвердит, что таблица действительно была очищена.
5. Это заблокирует вашу базу данных! Возможно, надолго! Для нашей таблицы с 20 миллионами записей это заняло 4 минуты.
Ответ №2:
Существует множество факторов, играющих большую роль в принятии PostgreSQL решения о том, как выполнить count() , но, прежде всего, столбец, который вы используете внутри count функции, не имеет значения. На самом деле, если вам не нужен DISTINCT count, придерживайтесь count(*) .
Вы можете попробовать следующее, чтобы принудительно выполнить сканирование только по индексу:
SELECT count(*) FROM (SELECT event_id FROM events) t;
…если это все еще приводит к последовательному сканированию, то, скорее всего, индекс не намного меньше самой таблицы. Чтобы по-прежнему видеть, как будет выполняться сканирование только по индексу, вы можете принудительно выполнить его с помощью:
SELECT count(*) FROM (SELECT event_id FROM events ORDER BY 1) t;
ЕСЛИ это не намного быстрее, вам также следует рассмотреть возможность обновления PostgreSQL по крайней мере до версии 9.6, которая вводит параллельные последовательные проверки для ускорения этих процессов.
Кроме того, вы можете добиться значительного ускорения, выбирая из множества методов предоставления подсчетов, которые в значительной степени зависят от вашего варианта использования и ваших требований:
И последнее, но не менее важное: пожалуйста, всегда предоставляйте выходные данные расширенного объяснения, как уже рекомендовано @a_horse_with_no_name, например:
EXPLAIN (ANALYZE, BUFFERS) SELECT count(event_id) FROM events;