Как сделать Python/PostgreSQL быстрее?

Не тратьте время на профилирование. Время всегда находится в операциях с базой данных. Делайте как можно меньше. Просто минимальное количество вставок.

Три вещи.

Один. Не нажимайте SELECT снова и снова, чтобы соответствовать параметрам Date, Hostname и Person. Извлечь все данные ОДИН РАЗ в словарь Python и использовать их в памяти. Не делайте повторяющиеся одноэлементные выборки. Используйте Питон.

Два. Не обновлять.

В частности, не делайте этого. Это плохой код по двум причинам.

cursor.execute("UPDATE people SET chats_count = chats_count + 1 WHERE id = '%s'" % person_id)

Его можно заменить простым SELECT COUNT(*) FROM ... . Никогда не обновляйте для увеличения счетчика. Просто подсчитайте строки, которые есть с оператором SELECT. [Если вы не можете сделать это с помощью простого SELECT COUNT или SELECT COUNT(DISTINCT), вы упускаете некоторые данные — ваша модель данных всегда должна обеспечивать правильные полные подсчеты. Никогда не обновлять.]

А также. Никогда не создавайте SQL, используя подстановку строк. Совершенно тупой.

Если по какой-то причине SELECT COUNT(*) недостаточно быстр (сначала оцените, прежде чем делать что-то хромое), вы можете кэшировать результат подсчета в другой таблице. ПОСЛЕ всех нагрузок. Сделайте SELECT COUNT(*) FROM whatever GROUP BY whatever и вставьте это в таблицу подсчетов. Не обновлять. Всегда.

Три. Используйте переменные привязки. Всегда.

cursor.execute( "INSERT INTO ... VALUES( %(x)s, %(y)s, %(z)s )", {'x':person_id, 'y':time_to_string(time), 'z':channel,} )

SQL никогда не меняется. Привязанные значения меняются, но SQL никогда не меняется. Это НАМНОГО быстрее. Никогда не создавайте операторы SQL динамически. Никогда.

Используйте переменные связывания вместо литеральных значений в операторах sql и создавайте курсор для каждого уникального оператора sql, чтобы оператор не нуждался в повторном анализе при следующем использовании. Из документа python db api:

Подготовьте и выполните операцию с базой данных (запрос или команду). Параметры могут быть предоставлены в виде последовательности или сопоставления и будут привязаны к переменным в операции. Переменные указываются в нотации, специфичной для базы данных (подробности см. в атрибуте paramstyle модуля). [5]

Ссылка на операцию останется за курсором. Если тот же объект операции передается снова, то курсор может оптимизировать свое поведение. Это наиболее эффективно для алгоритмов, в которых используется одна и та же операция, но к ней привязаны разные параметры (много раз).

ВСЕГДА ВСЕГДА ВСЕГДА используйте переменные связывания.

В цикле for вы неоднократно вставляете данные в таблицу «чаты», поэтому вам нужен только один оператор sql с переменными связывания, который будет выполняться с разными значениями. Таким образом, вы можете поместить это перед циклом for:

insert_statement="""
    INSERT INTO chats(person_id, message_type, created_at, channel)
    VALUES(:person_id,:message_type,:created_at,:channel)
"""

Затем вместо каждого оператора sql, который вы выполняете, поместите это на место:

cursor.execute(insert_statement, person_id='person',message_type='msg',created_at=some_date, channel=3)

Это ускорит работу, потому что:

1. Объект курсора не должен будет каждый раз повторно анализировать оператор
2. Серверу базы данных не нужно будет создавать новый план выполнения, поскольку он может использовать тот, который он создал ранее.
3. Вам не нужно будет вызывать santitize(), так как специальные символы в переменных связывания не будут частью выполняемого оператора sql.

Примечание. Синтаксис переменной связывания, который я использовал, специфичен для Oracle. Вам нужно будет проверить документацию библиотеки psycopg2 для точного синтаксиса.

Другие оптимизации:

1. Вы увеличиваете значение «UPDATE people SET chatscount» после каждой итерации цикла. Держите пользователя сопоставления словаря с chat_count, а затем выполните оператор общего числа, которое вы видели. Это будет быстрее, чем попадание в БД после каждой записи.
2. Используйте переменные привязки для ВСЕХ ваших запросов. Не только оператор вставки, я выбрал его в качестве примера.
3. Измените все функции find_*(), которые выполняют поиск в базе данных, чтобы кэшировать их результаты, чтобы им не приходилось каждый раз обращаться к базе данных.
4. Психо оптимизирует программы на Python, которые выполняют большое количество числовых операций. Сценарий требует больших затрат на ввод-вывод и не требует больших ресурсов процессора, поэтому я не ожидаю, что он даст вам много, если какая-либо оптимизация.

Как предложил Марк, используйте переменные привязки. База данных должна подготовить каждый оператор только один раз, а затем «заполнить пробелы» для каждого выполнения. В качестве приятного побочного эффекта он автоматически позаботится о проблемах со строковыми кавычками (которые ваша программа не обрабатывает).

Включите транзакции (если они еще не включены) и выполните единственную фиксацию в конце программы. Базе данных не придется ничего записывать на диск, пока все данные не будут зафиксированы. И если ваша программа обнаружит ошибку, ни одна из строк не будет зафиксирована, что позволит вам просто перезапустить программу, как только проблема будет устранена.

Ваши функции log_hostname, log_person и log_date выполняют ненужные операции SELECT над таблицами. Сделайте соответствующие атрибуты таблицы PRIMARY KEY или UNIQUE. Затем, вместо проверки наличия ключа перед INSERT, просто выполните INSERT. Если человек/дата/имя хоста уже существуют, INSERT завершится ошибкой из-за нарушения ограничения. (Это не сработает, если вы используете транзакцию с одной фиксацией, как было предложено выше.)

В качестве альтернативы, если вы знаете, что вы единственный, кто выполняет INSERT в таблицы во время работы вашей программы, создайте параллельные структуры данных в памяти и сохраняйте их в памяти, пока вы выполняете INSERT. Например, считать все имена хостов из таблицы в ассоциативный массив в начале программы. Если вы хотите знать, делать ли INSERT, просто выполните поиск в массиве. Если запись не найдена, выполните INSERT и соответствующим образом обновите массив. (Это предложение совместимо с транзакциями и одной фиксацией, но требует больше программирования. Однако это будет ужасно быстрее.)

В дополнение ко многим прекрасным предложениям, которые дал @Mark Roddy, сделайте следующее:

• не используйте readlines, вы можете перебирать файловые объекты
• попробуйте использовать executemany, а не execute: попробуйте делать пакетные вставки, а не одиночные вставки, это, как правило, быстрее, потому что меньше накладных расходов. Это также уменьшает количество коммитов
• str.rstrip будет работать нормально вместо удаления новой строки с помощью регулярного выражения

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