Почему запросы к pandas DataFrame медленнее после операции соединения SQL?

TLDR о явлении; из приведенного ниже эксперимента мы видим, что pd.read_sql() требуется пропорционально больше памяти, чем pd.read_csv, где разница в использовании памяти увеличивается с размером набора данных. Поскольку разница легко переходит в гигабайты для наборов данных с> 1 миллионом строк, эта разница может быть достаточно большой, чтобы повлиять на производительность последующих операций при использовании pd.read_sql.

Поскольку оба DataFrames используют одинаковый объем памяти после обеспечения четности dtype, и учитывая, что накладные расходы памяти при создании экземпляра DB engine не зависят от размера загружаемого набора данных, результаты подтверждают гипотезу о том, что pd.read_sql() пропорционально больше memory-intensive, чем pd.read_csv .

Рекомендация для OP: учитывая выявленное поведение pd.read_sql(), вероятно, самым простым решением будет сохранить данные как .csv и загрузить их впоследствии. Возможно, вы можете разделить свою программу на два отдельных сценария: первая часть загружает и сохраняет данные, а вторая выполняет запросы.

Подход:

Я использовал ответ SO здесь, чтобы выяснить, сколько памяти использует Python, запустив следующие два фрагмента кода в блокноте Jupyter.

import os
import psutil
import sys

import psycopg2
import pandas as pd
import numpy as np

dbname = "postgres"
user = "postgres"
password = "root"
host = "localhost"
port = "5432"

connection_string = f"dbname = {dbname} user = {user} password = {password} host = {host} port = {port}"
conn = psycopg2.connect(connection_string)

df = pd.read_sql("SELECT * FROM staging_spread", con=conn)

process = psutil.Process()
print(df.shape)  # (195976, 6)
print(process.memory_info().rss/(1024*1024))  # 246.49 MB
print(sys.getsizeof(df)/(1024*1024))  # 30.1 MB

И после перезапуска ядра:

import os
import psutil
import sys

import psycopg2
import pandas as pd
import numpy as np

df2 = pd.read_csv("example.csv", parse_dates=['crawl_ts', 'market_ts'])

process = psutil.Process()
print(df2.shape)  # (195976, 6)
print(process.memory_info().rss/(1024*1024))  # 112.9 MB
print(sys.getsizeof(df2)/(1024*1024))  # 30.1 MB

Загруженный мной набор данных содержит почти 200 000 записей. Я провел тот же эксперимент, используя записи 25 000, 50 000, 75 000 и 100 000. Результаты общего требуемого объема памяти показаны ниже:

|   N Rows |   Total Memory pd.read_sql() |   Total Memory pd.read_csv() |   Abs Diff |   Diff Increase |
|---------:|-----------------------------:|-----------------------------:|-----------:|----------------:|
|   100000 |                       172.77 |                      101.676 |    71.0942 |         18.3342 |
|    75000 |                       151.3  |                       98.54  |    52.76   |         17.1    |
|    50000 |                       130.26 |                       94.6   |    35.66   |         18.37   |
|    25000 |                       108.56 |                       91.27  |    17.29   |        nan      |

Из этой таблицы видно, что на каждые дополнительные 25 000 строк подход с использованием pd.read_sql() требует дополнительных 18 мегабайт. Теперь, если мы экстраполируем этот результат на 1 миллион строк, подход pd.read_sql() требует на 720 МБ больше, чем подход pd.read_csv() для этого набора данных. Набор данных, который я использовал, содержал только 6 столбцов — разница, конечно, также будет увеличиваться, когда загружаются наборы данных с большим количеством столбцов!

Дополнительная информация: df.info() для обоих наборов данных показана четность наборов данных.

df.info() для df (создано с использованием pd.read_sql())

print(df.info())
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 195976 entries, 0 to 195975
Data columns (total 6 columns):
 #   Column           Non-Null Count   Dtype         
---  ------           --------------   -----         
 0   crawl_ts         195976 non-null  datetime64[ns]
 1   crawler_version  195976 non-null  object        
 2   ticker           195976 non-null  object        
 3   market_ts        195976 non-null  datetime64[ns]
 4   bid              195976 non-null  float64       
 5   ask              195976 non-null  float64       
dtypes: datetime64[ns](2), float64(2), object(2)
memory usage: 9.0+ MB
None

df.info() для df2 (создано с помощью pd.read_csv())

print(df2.info())
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 195976 entries, 0 to 195975
Data columns (total 6 columns):
 #   Column           Non-Null Count   Dtype         
---  ------           --------------   -----         
 0   crawl_ts         195976 non-null  datetime64[ns]
 1   crawler_version  195976 non-null  object        
 2   ticker           195976 non-null  object        
 3   market_ts        195976 non-null  datetime64[ns]
 4   bid              195976 non-null  float64       
 5   ask              195976 non-null  float64       
dtypes: datetime64[ns](2), float64(2), object(2)
memory usage: 9.0+ MB
None