#postgresql #postgresql-performance
#postgresql #postgresql-производительность
Вопрос:
У меня есть оператор, который вставляет кучу строк (на самом деле не имеет значения, что или где для этого вопроса) в Postgres DB, и это происходит не так быстро, как хотелось бы. Я могу запустить запрос explain, чтобы посмотреть, что он делает, и я получаю что-то вроде этого:
Insert on dpdb.datapoints (cost=0.00..6917.76 rows=44184 width=1786) (actual time=15558.623..15558.623 rows=0 loops=1)
Buffers: shared hit=34670391 read=98370 dirtied=48658 written=39875
I/O Timings: read=704.525 write=242.915
-> Seq Scan on public.fred (cost=0.00..6917.76 rows=44184 width=1786) (actual time=0.018..197.853 rows=44184 loops=1)
Output: nextval('datapoints_id_seq'::regclass), fred.company_id, fred.tag, ... lots more columns ...
Buffers: shared hit=44186 read=6253 dirtied=1
I/O Timings: read=29.176
Planning time: 0.110 ms
Trigger RI_ConstraintTrigger_c_14845718 for constraint datapoints_tag_source_fkey: time=236.677 calls=44184
Trigger RI_ConstraintTrigger_c_14845723 for constraint datapoints_sheet_type_fkey: time=536.367 calls=44184
Trigger RI_ConstraintTrigger_c_14845728 for constraint datapoints_subcontext_fkey: time=178.200 calls=44184
Trigger RI_ConstraintTrigger_c_14845733 for constraint datapoints_source_type_fkey: time=467.619 calls=44184
Trigger RI_ConstraintTrigger_c_14845738 for constraint datapoints_doc_type_fkey: time=302.256 calls=44184
Trigger RI_ConstraintTrigger_c_14845743 for constraint datapoints_comment_type_fkey: time=88.740 calls=44184
Trigger RI_ConstraintTrigger_c_14845748 for constraint datapoints_preferred_dpid_fkey: time=33.313 calls=44184
Execution time: 17432.381 ms
(16 rows)
Это здорово тем, что я вижу стоимость различных триггеров и части запроса select, но если я сложу все эти части, это займет около 2 секунд (менее 10% от общего времени). Это мало что говорит мне о фактической вставке, кроме того, что это заняло около 24 секунд. Я предполагаю, что значительная часть затрат связана с обновлением индексов, но я не знаю этого наверняка. Как я могу получить разбивку того, какие индексы обновляются, и относительные затраты на каждый из них? Возможно ли это вообще?
(Первоначально я спрашивал об этом на dba.stackexchange.com но не получил никаких ответов — он находится на полпути между вопросом администратора базы данных и разработчика, поэтому я решил спросить здесь)
Комментарии:
1. Насколько я знаю, для каждого индекса нет статистики, связанной со временем. вы можете удалить все индексы и создать один. Обновление раздутых индексов может быть медленным — знаете ли вы инструкцию REINDEX?
2. Хорошей отправной точкой является включение track_io_timing, а затем запуск EXPLAIN (АНАЛИЗ, БУФЕРЫ). Если вы делаете это несколько сотен раз подряд, согласуется ли время?
3. Я включил track_io_timing, повторно запустил тесты и соответствующим образом обновил вопрос. Статистика полезна тем, что она подтверждает предполагаемую проблему ввода-вывода. Если я удаляю данные после каждой вставки, я получаю примерно одинаковые результаты. Добавление новых вставок поверх старых приводит к еще худшей производительности (одна из причин, по которой я пытаюсь выяснить, почему)
4. Спасибо за совет по переиндексации — я не рассматривал его, но в данном случае это не имеет существенного значения.
Ответ №1:
Это похоже на ограничения внешнего ключа. Каждая вставка выполняет SELECT проверку, чтобы увидеть, присутствует ли строка, мы надеемся, используя индекс, предполагающий, что индекс присутствует.
Расхождение во времени связано с тем, что time каждый триггер показывает вам количество времени, затраченного на выполнение функции, и исключает время ожидания ввода-вывода. Другими словами, он показывает вам не время настенных часов, а время процессора (приблизительно).
Вы можете использовать pg_stat_statements, чтобы попытаться просмотреть запросы к этим таблицам и найти внешние ключевые, но, в конце концов, это выглядит довольно нормально для работы с внешними ключами.
Если вам нужно, чтобы эти вставки выполнялись быстрее, лучшим вариантом было бы удалить ограничения внешнего ключа.
Комментарии:
1. Спасибо. Хотя я не совсем уверен, что понимаю. Как мне запросить pg_stats для чего-либо информативного о времени поиска? Кроме того, если я правильно понимаю, вы говорите, что «фактическое время = 15558,623 ..15558,623» сообщает о времени в 15 секунд, но «datapoints_tag_source_fkey: time = 236,677» означает .2 секунды процессорного времени. Предполагая, что это так, есть ли способ согласовать два значения? (Я действительно не возражаю, если это постоянное процессорное время или постоянное время работы)
2. Извините, я пропустил, я имел в виду использование pg_stat_statements. Я обновлю ответ.
3. Что касается вашего второго вопроса, нет, я в это не верю, если вы не хотите исправлять и перекомпилировать Postgres, чтобы они соответствовали. Но процессорное время для всего этого в любом случае было бы не очень полезным, поэтому оно показывает время на стене.
4. Так лучше, спасибо. Для дальнейшего использования это полезно только в том случае, если модуль установлен (он находится в contrib и не всегда доступен по умолчанию) и если для pg_stat_statements.track установлено значение «все».