python - 为什么 SQL 聚合函数比 Python 和 Java(或穷人的 OLAP)慢得多

Question

我需要一个真正的 DBA 的意见。 Postgres 8.3 在我的 Macbook Pro 上执行此查询需要 200 毫秒，而 Java 和 Python 执行相同的计算不到 20 毫秒(350,000 行):

SELECT count(id), avg(a), avg(b), avg(c), avg(d) FROM tuples;

这是使用 SQL 数据库时的正常行为吗？

架构(表格包含对调查的回复):

CREATE TABLE tuples (id integer primary key, a integer, b integer, c integer, d integer);

\copy tuples from '350,000 responses.csv' delimiter as ','

我用 Java 和 Python 为上下文编写了一些测试，它们压垮了 SQL(纯 Python 除外):

java   1.5 threads ~ 7 ms    
java   1.5         ~ 10 ms    
python 2.5 numpy   ~ 18 ms  
python 2.5         ~ 370 ms

即使 sqlite3 与 Postgres 竞争，尽管它假设所有列都是字符串(相比之下:即使仅使用数字列而不是 Postgres 中的整数也会导致 10 倍的减速)

我试过但没有成功的调整包括(盲目地遵循一些网络建议):

increased the shared memory available to Postgres to 256MB    
increased the working memory to 2MB
disabled connection and statement logging
used a stored procedure via CREATE FUNCTION ... LANGUAGE SQL

所以我的问题是，我在这里的体验是否正常，这是我在使用 SQL 数据库时可以期待的吗？我能理解 ACID 必须付出代价，但在我看来这有点疯狂。我不是要求实时游戏速度，但由于 Java 可以在 20 毫秒内处理数百万次 double ，我感到有点嫉妒。

有没有更好的方法来以低廉的成本(在金钱和服务器复杂性方面)进行简单的 OLAP？我研究了 Mondrian 和 Pig + Hadoop，但对维护另一个服务器应用程序并不太兴奋，不确定它们是否能提供帮助。

---

可以这么说，没有 Python 代码和 Java 代码在内部完成所有工作。我只是生成 4 个数组，每个数组有 350,000 个随机值，然后取平均值。我没有在计时中包括生成，只有平均步骤。 Java 线程计时使用 4 个线程(每个数组平均一个线程)，有点矫枉过正，但它绝对是最快的。

sqlite3 时序由 Python 程序驱动，从磁盘运行(不是 :memory:)

我意识到 Postgres 在幕后做了更多工作，但大部分工作对我来说并不重要，因为这是只读数据。

Postgres 查询不会更改后续运行的时间。

我已经重新运行 Python 测试以包括将其从磁盘假脱机。时间大大减慢到近 4 秒。但我猜 Python 的文件处理代码几乎都是用 C 编写的(尽管可能不是 csv 库？)所以这向我表明 Postgres 也不是从磁盘流式传输的(或者你是对的，我应该鞠躬在编写存储层的人之前!)

Answer 1

我会说你的测试方案并不是很有用。为了完成数据库查询，数据库服务器经过几个步骤:

1. 解析SQL
2. 制定一个查询计划，i。 e.决定使用哪些索引(如果有的话)、优化等。
3. 如果使用索引，则在其中搜索指向实际数据的指针，然后转到数据中的适当位置或
4. 如果没有使用索引，则扫描整个表以确定需要哪些行
5. 将数据从磁盘加载到一个临时位置(希望，但不一定是内存)
6. 执行 count() 和 avg() 计算

因此，在 Python 中创建数组并获取平均值基本上会跳过所有这些步骤，只剩下最后一个。由于磁盘 I/O 是程序必须执行的最昂贵的操作之一，这是测试中的一个主要缺陷(另请参阅我之前在这里问过的 this question 的答案)。即使您在其他测试中从磁盘读取数据，过程也完全不同，并且很难判断结果的相关性。

要获得有关 Postgres 花费时间的更多信息，我建议进行以下测试:

• 将查询的执行时间与没有聚合函数的 SELECT 的执行时间进行比较(即削减第 5 步)
• 如果您发现聚合导致显着减速，请尝试 Python 是否更快，通过比较中的普通 SELECT 获取原始数据。

要加快查询速度，请先减少磁盘访问。我非常怀疑是聚合耗费了时间。

有几种方法可以做到这一点:

• 缓存数据(在内存中!)以供后续访问，通过数据库引擎自身的功能或使用 memcached 等工具
• 减少存储数据的大小
• 优化索引的使用。有时这可能意味着完全跳过索引使用(毕竟，它也是磁盘访问)。对于 MySQL，我似乎记得如果您假设查询获取表中所有数据的 10% 以上，建议跳过索引。
• 如果您的查询充分利用了索引，我知道对于 MySQL 数据库来说，将索引和数据放在不同的物理磁盘上会有所帮助。但是，我不知道这是否适用于 Postgres。
• 还可能存在更复杂的问题，例如如果由于某种原因无法在内存中完全处理结果集，则将行交换到磁盘。但在我遇到无法找到另一种方法来解决的严重性能问题之前，我会放弃这种研究，因为它需要了解您流程中的许多底层细节。

更新:

我刚刚意识到您似乎对上述查询没有使用索引，而且很可能也没有使用任何索引，所以我对索引的建议可能没有帮助。对不起。尽管如此，我还是要说聚合不是问题，但磁盘访问才是问题。索引的东西我会留下来，反正它可能还有一些用处。