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

我需要一个真正的DBA的意见.Postgres 8.3在我的Macbook Pro上执行此查询需要200 ms,而Java和Python在20 ms(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中的整数导致10x减速)

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

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毫秒内处理数百万的双打,我感到有点嫉妒.

是否有更好的方法以便宜的方式进行简单的OLAP(在金钱和服务器复杂性方面)？我已经研究过Mondrian和Pig + Hadoop,但对于维护另一台服务器应用程序并不确定它们是否会提供帮助并不是非常兴奋.

---

没有Python代码和Java代码可以完成内部的所有工作.我只生成4个阵列,每个阵列有350,000个随机值,然后取平均值.我不在时间中包括生成,只包括平均步骤.java线程计时使用4个线程(每个阵列平均一个),过度杀伤但它绝对是最快的.

sqlite3时序由Python程序驱动,并从磁盘运行(不是:内存:)

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

Postgres查询不会改变后续运行的时间.

我重新运行Python测试以包括将其从磁盘中删除.时间大大减慢到近4秒.但我猜测Python的文件处理代码几乎是在C语言中(虽然可能不是csv lib？)所以这向我表明Postgres也没有从磁盘流式传输(或者你是正确的我应该鞠躬在谁写了他们的存储层之前!)

1> Hanno Fietz..：

---

我会说你的测试方案并不真正有用.要完成db查询,db服务器将执行以下几个步骤:

解析SQL

制定查询计划,即决定使用哪些指数(如果有的话),优化等.

如果使用索引,则搜索指向实际数据的指针,然后转到数据中的适当位置或

如果没有使用索引,则扫描整个表以确定需要哪些行

将数据从磁盘加载到临时位置(希望,但不一定,内存)

执行count()和avg()计算

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

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

将查询的执行时间与没有聚合函数的SELECT进行比较(即切换步骤5)

如果您发现聚合导致显着减速,请尝试Python更快地执行此操作,通过比较中的普通SELECT获取原始数据.

要加快查询速度,请首先减少磁盘访问.我非常怀疑这是花费时间的聚合.

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

缓存数据(在内存中!),以便通过db引擎自身的功能或使用memcached等工具进行后续访问

减少存储数据的大小

优化指数的使用.有时这可能意味着完全跳过索引使用(毕竟,它也是磁盘访问).对于MySQL,我似乎记得如果你假设查询占据表中所有数据的10%以上,建议跳过索引.

如果您的查询充分利用索引,我知道对于MySQL数据库,它有助于将索引和数据放在不同的物理磁盘上.但是,我不知道这是否适用于Postgres.

如果由于某种原因结果集无法在内存中完全处理,也可能存在更复杂的问题,例如将行交换到磁盘.但是我会留下那种研究,直到我遇到严重的性能问题,我找不到另一种方法来修复,因为它需要了解你的过程中很多小的底层细节.

更新:

我刚刚意识到你似乎没有使用上述查询的索引,并且很可能也没有使用任何索引,所以我对索引的建议可能没有帮助.抱歉.不过,我会说聚合不是问题,但磁盘访问是.我会留下索引的东西,无论如何,它可能还有一些用处.

---

以确定需要哪些行

---

---

1. Python还需要解析"查询".如果重复运行SQL,则应缓存该步骤.2. Python还需要编写一个"查询计划" - 也就是说,如果是JIT,虽然这比SQL更容易.同样,如果重复运行,应该缓存.3.没有可能使用的索引4.Python还需要扫描*整个表* - 350k行的一项微不足道的任务.5.根据你获取数据的位置,python也需要从磁盘加载数据 - 再次,如果重复运行,这应该被缓存. - 基本上,这种简单案例的差异几乎为零.

2> Matthew Wats..：

---

Postgres做得比看起来要多得多(维护数据的一致性!)

如果值不必须是100%正确的,或者如果表很少更新,但你经常跑这个计算,你可能想看看物化视图,以加速这一过程.

(请注意,我没有用在Postgres的物化视图,他们看小哈克,但可能适合你的情况).

物化观点

还要考虑实际连接到服务器的开销以及将请求发送到服务器并返回所需的往返.

我会考虑这样的事情200ms的是相当不错的,我的Oracle服务器上快速测试,大约50万行,没有索引相同的表结构,大约需要1 - 1.5秒,这几乎是所有只是甲骨文吸吮数据关闭磁盘.

真正的问题是,200毫秒足够快吗？

- - - - - - - 更多 - - - - - - - - - -

我有兴趣使用物化视图来解决这个问题,因为我从未真正使用它们.这是在甲骨文.

首先,我创建了一个每分钟刷新一次的MV.

create materialized view mv_so_x 
build immediate 
refresh complete 
START WITH SYSDATE NEXT SYSDATE + 1/24/60
 as select count(*),avg(a),avg(b),avg(c),avg(d) from so_x;

虽然它令人耳目一新,但没有返回任何行

SQL> select * from mv_so_x;

no rows selected

Elapsed: 00:00:00.00

刷新后,它比原始查询更快

SQL> select count(*),avg(a),avg(b),avg(c),avg(d) from so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:05.74
SQL> select * from mv_so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:00.00
SQL> 

如果我们插入基表,结果就不能立即查看MV.

SQL> insert into so_x values (1,2,3,4,5);

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select * from mv_so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:00.00
SQL> 

但是等一下左右,MV会在幕后更新,结果可以根据需要快速返回.

SQL> /

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899460 7495.35823 22.2905352 5.00276078 2.17647059

Elapsed: 00:00:00.00
SQL> 

这不太理想.一开始,它不是实时的,插入/更新将不会立即可见.此外,无论您是否需要,您都可以运行查询来更新MV(这可以调整到任何时间范围或按需).但是,这确实表明了MV可以让最终用户看起来更快,如果你可以使用不高于第二精确度的值.

3> Jacob Rigby..：

---

我重新测试了MySQL指定ENGINE = MEMORY并且它没有改变一件事(仍然是200毫秒).使用内存数据库的Sqlite3也提供了类似的时序(250毫秒).

这里的数学看起来是正确的(至少是大小,因为sqlite数据库有多大:-)

我只是没有购买disk-cause-slowness参数,因为每个迹象表明这些表都在内存中(postgres的所有人都警告不要过于努力将表格固定到内存中,因为他们发誓操作系统会比程序员做得更好)

为了澄清定时,Java代码不从磁盘读取,使其成为一个完全不公平的比较,如果Postgres的从磁盘读取和计算复杂的查询,但是这是真的,除了这一点,数据库应该足够聪明,带上小表进入内存并预编译存储过程恕我直言.

更新(回应下面的第一条评论):

我不知道我怎么会测试查询,而无需在某种程度上,这将是公平的,因为如果我选择所有的行,它会花费大量的时间,序列化和格式化的一切使用聚合函数.我并不是说缓慢是由聚合函数引起的,它可能只是来自并发性,完整性和朋友的开销.我只是不知道如何将聚合隔离为唯一的自变量.