草庐IT

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

coder 2023-08-13 原文

我需要一个真正的 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 也不是从磁盘流式传输的(或者你是对的，我应该鞠躬在编写存储层的人之前!)

最佳答案

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

  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。
  • 还可能存在更复杂的问题，例如如果由于某种原因无法在内存中完全处理结果集，则将行交换到磁盘。但在我遇到无法找到另一种方法来解决的严重性能问题之前，我会放弃这种研究，因为它需要了解您流程中的许多底层细节。

更新:

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

关于python - 为什么 SQL 聚合函数比 Python 和 Java(或穷人的 OLAP)慢得多，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/51553/

穷人pythonPostgreslisqloptimizationaggregateolap

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

  1. ruby - 为什么我可以在 Ruby 中使用 Object#send 访问私有(private)/ protected 方法？ - 2

    类classAprivatedeffooputs:fooendpublicdefbarputs:barendprivatedefzimputs:zimendprotecteddefdibputs:dibendendA的实例a=A.new测试a.foorescueputs:faila.barrescueputs:faila.zimrescueputs:faila.dibrescueputs:faila.gazrescueputs:fail测试输出failbarfailfailfail.发送测试[:foo,:bar,:zim,:dib,:gaz].each{|m|a.send(m)resc

  2. python - 如何使用 Ruby 或 Python 创建一系列高音调和低音调的蜂鸣声？ - 2

    关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。

  3. ruby-on-rails - Rails - 子类化模型的设计模式是什么？ - 2

    我有一个模型:classItem项目有一个属性“商店”基于存储的值，我希望Item对象对特定方法具有不同的行为。Rails中是否有针对此的通用设计模式？如果方法中没有大的if-else语句，这是如何干净利落地完成的？ 最佳答案 通常通过Single-TableInheritance. 关于ruby-on-rails-Rails-子类化模型的设计模式是什么？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.co

  4. ruby - 什么是填充的 Base64 编码字符串以及如何在 ruby​​ 中生成它们？ - 2

    我正在使用的第三方API的文档状态:"[O]urAPIonlyacceptspaddedBase64encodedstrings."什么是“填充的Base64编码字符串”以及如何在Ruby中生成它们。下面的代码是我第一次尝试创建转换为Base64的JSON格式数据。xa=Base64.encode64(a.to_json) 最佳答案 他们说的padding其实就是Base64本身的一部分。它是末尾的“=”和“==”。Base64将3个字节的数据包编码为4个编码字符。所以如果你的输入数据有长度n和n%3=1=>"=="末尾用于填充n%

  5. ruby - 解析 RDFa、微数据等的最佳方式是什么，使用统一的模式/词汇(例如 schema.org)存储和显示信息 - 2

    我主要使用Ruby来执行此操作，但到目前为止我的攻击计划如下:使用gemsrdf、rdf-rdfa和rdf-microdata或mida来解析给定任何URI的数据。我认为最好映射到像schema.org这样的统一模式，例如使用这个yaml文件，它试图描述数据词汇表和opengraph到schema.org之间的转换:#SchemaXtoschema.orgconversion#data-vocabularyDV:name:namestreet-address:streetAddressregion:addressRegionlocality:addressLocalityphoto:i

  6. ruby - 为什么 4.1%2 使用 Ruby 返回 0.0999999999999996？但是 4.2%2==0.2 - 2

    为什么4.1%2返回0.0999999999999996？但是4.2%2==0.2。 最佳答案 参见此处:WhatEveryProgrammerShouldKnowAboutFloating-PointArithmetic实数是无限的。计算机使用的位数有限(今天是32位、64位)。因此计算机进行的浮点运算不能代表所有的实数。0.1是这些数字之一。请注意，这不是与Ruby相关的问题，而是与所有编程语言相关的问题，因为它来自计算机表示实数的方式。 关于ruby-为什么4.1%2使用Ruby返

  7. ruby - ruby 中的 TOPLEVEL_BINDING 是什么？ - 2

    它不等于主线程的binding，这个toplevel作用域是什么？此作用域与主线程中的binding有何不同？>ruby-e'putsTOPLEVEL_BINDING===binding'false 最佳答案 事实是，TOPLEVEL_BINDING始终引用Binding的预定义全局实例，而Kernel#binding创建的新实例>Binding每次封装当前执行上下文。在顶层，它们都包含相同的绑定(bind)，但它们不是同一个对象，您无法使用==或===测试它们的绑定(bind)相等性。putsTOPLEVEL_BINDINGput

  8. ruby - Infinity 和 NaN 的类型是什么？ - 2

    我可以得到Infinity和NaNn=9.0/0#=>Infinityn.class#=>Floatm=0/0.0#=>NaNm.class#=>Float但是当我想直接访问Infinity或NaN时:Infinity#=>uninitializedconstantInfinity(NameError)NaN#=>uninitializedconstantNaN(NameError)什么是Infinity和NaN？它们是对象、关键字还是其他东西？ 最佳答案 您看到打印为Infinity和NaN的只是Float类的两个特殊实例的字符串

  9. ruby-on-rails - 如果 Object::try 被发送到一个 nil 对象，为什么它会起作用？ - 2

    如果您尝试在Ruby中的nil对象上调用方法，则会出现NoMethodError异常并显示消息:"undefinedmethod‘...’fornil:NilClass"然而，有一个tryRails中的方法，如果它被发送到一个nil对象，它只返回nil:require'rubygems'require'active_support/all'nil.try(:nonexisting_method)#noNoMethodErrorexceptionanymore那么try如何在内部工作以防止该异常？ 最佳答案 像Ruby中的所有其他对象

  10. ruby - 为什么 SecureRandom.uuid 创建一个唯一的字符串？ - 2

    关闭。这个问题需要detailsorclarity.它目前不接受答案。想改进这个问题吗？通过editingthispost添加细节并澄清问题.关闭8年前。Improvethisquestion为什么SecureRandom.uuid创建一个唯一的字符串？SecureRandom.uuid#=>"35cb4e30-54e1-49f9-b5ce-4134799eb2c0"SecureRandom.uuid方法创建的字符串从不重复？

随机推荐