Dataframe 写入 Postgresql 性能不佳

在 postgresql 中工作，我有一个笛卡尔连接，生成约 400 万行。 连接需要约 5 秒，写回数据库需要约 1 分 45 秒。

这些数据需要在 python 中使用，特别是在 pandas 数据框中，因此我正在尝试在 python 中复制相同的数据。我应该在这里说，所有这些测试都在一台机器上运行，因此没有任何内容通过网络进行。

使用 psycopg2 和 pandas，读入数据并执行连接以获得 400 万行（来自此处的答案：pandas 中的笛卡尔积 https://stackoverflow.com/questions/13269890/cartesian-product-in-pandas）持续花费不到 3 秒，令人印象深刻。

然而，将数据写回数据库中的表需要 8 分钟（最佳方法）到 36 分钟以上（加上我拒绝的一些方法，因为我必须在 >1 小时后停止它们）。

虽然我没想到会重现“仅 sql”时间，但我希望能够接近 8 分钟（我认为 3-5 分钟不会不合理）。

较慢的方法包括：

36分钟 - sqlalchemy`s table.insert（来自此处的“test_sqlalchemy_core”https://docs.sqlalchemy.org/en/latest/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow https://docs.sqlalchemy.org/en/latest/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow)

13分钟-psycopg2.extras.execute_batch（https://stackoverflow.com/a/52124686/3979391 https://stackoverflow.com/a/52124686/3979391)

13-15分钟（取决于块大小）-pandas.dataframe.to_sql（再次使用sqlalchemy）（https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html)

最好的方法（〜8分钟）是使用psycopg2的cursor.copy_from方法（在这里找到：https://github.com/blaze/odo/issues/614#issuecomment-428332541 https://github.com/blaze/odo/issues/614#issuecomment-428332541）。 这涉及到首先将数据转储到 csv（通过 io.StringIO 在内存中），仅此一项就需要 2 分钟。

所以，我的问题是：

1. 任何人都有可能更快地将数百万行从 pandas 数据帧写入 postgresql 的方法吗？

2. cursor.copy_from 方法的文档（http://initd.org/psycopg/docs/cursor.html http://initd.org/psycopg/docs/cursor.html）声明源对象需要支持 read() 和 readline() 方法（因此需要 io.StringIO）。据推测，如果数据框支持这些方法，我们就可以省去写入 csv。有什么方法可以添加这些方法吗？

谢谢。 贾尔斯

EDIT:

在第二季度 - pandas 现在可以使用 to_sql 的自定义可调用对象以及此处给出的示例：https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#io-sql-method https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#io-sql-method几乎按照我上面的建议进行操作（即它使用 StringIO 直接从 STDIN 复制 csv 数据）。 我发现使用此方法写入速度提高了约 40%，这使 to_sql 接近上面提到的“最佳”方法。

我自己回答问题1： 看来这个问题更多地与 Postgresql（或者更确切地说是数据库）有关。考虑到本文中提出的观点：https://use-the-index-luke.com/sql/dml/insert https://use-the-index-luke.com/sql/dml/insert我发现了以下内容：

1) 从目标表中删除所有索引导致查询在 9 秒内运行。重建索引（在 postgresql 中）又花了 12 秒，所以仍然远远低于其他时间。

2) 在仅存在主键的情况下，插入按主键列排序的行将所需时间减少到大约三分之一。这是有道理的，因为应该很少或不需要对索引行进行改组。我还验证了这就是为什么我在 postgresql 中的笛卡尔连接首先速度更快的原因（IE 行是按索引排序的，纯粹是偶然的），将相同的行放在临时表中（无序）并从中插入实际上花了更长的时间。

3）我在我们的mysql系统上尝试了类似的实验，发现删除索引时插入速度也有同样的提高。然而，对于mysql来说，重建索引似乎耗尽了所获得的任何时间。

我希望这对通过搜索遇到此问题的其他人有所帮助。

我仍然想知道是否可以删除 python 中的写入 csv 步骤（上面的 Q2），因为我相信我可以在 python 中编写比纯 postgresql 更快的东西。

谢谢，贾尔斯