sql-server – 对于绝对性能,SUM更快还是COUNT？

我倾向于喜欢

COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)

但是,这同样有效

SUM(CASE WHEN invoice_amount > 100 THEN 1 ELSE 0 END)

我原以为COUNT是有利的,原因有两个：

>传达意图,即COUNT
> COUNT可能涉及
一个简单的i = 1操作,而SUM不能指望
它的表达式是一个简单的整数值.

有没有人有关于特定RDBMS差异的具体事实？

解决方法

在PostgreSQL(和其他支持布尔类型的RDBMS)中,您可以直接使用测试的布尔结果.将它转换为整数和SUM()：

SUM((amount > 100)::int))

或者在NULLIF()表达式和COUNT()中使用它：

COUNT(NULLIF(amount > 100,FALSE))

或者使用简单的OR NULL：

COUNT(amount > 100 OR NULL)

或其他各种表达方式.性能几乎完全相同. COUNT()通常比SUM()快一点.与SUM()和Paul already commented不同,COUNT()永远不会返回NULL,这可能很方便.有关：

> Query optimization or missing indexes?

从Postgres 9.4开始,还有FILTER子句.细节：

> Return counts for multiple ranges in a single SELECT statement

它比上述所有速度快5到10％：

COUNT(*) FILTER (WHERE amount > 100)

如果查询与测试用例一样简单,只有一个计数而没有其他任何内容,则可以重写：

SELECT count(*) FROM tbl WHERE amount > 100;

即使没有索引,哪个是真正的表演之王.
使用适用的索引,它可以更快的数量级,特别是对于仅索引扫描.

基准

Postgres 10

我为Postgres 10运行了一系列新的测试,包括聚合的FILTER子句,并展示了一个小指数和大指数的作用.

设置简单：

CREATE TABLE tbl (
   tbl_id int,amount int NOT NULL
);

INSERT INTO tbl
SELECT g,(random() * 150)::int
FROM   generate_series (1,1000000) g;

-- only relevant for the last test
CREATE INDEX ON tbl (amount);

由于背景噪音和试验台的具体情况,实际时间变化很大.从更大的测试集中显示典型的最佳时间.这两个案例应该抓住本质：

测试1计算所有行的~1％

SELECT COUNT(NULLIF(amount > 148,FALSE))            FROM tbl; -- 140 ms
SELECT SUM((amount > 148)::int)                      FROM tbl; -- 136 ms
SELECT SUM(CASE WHEN amount > 148 THEN 1 ELSE 0 END) FROM tbl; -- 133 ms
SELECT COUNT(CASE WHEN amount > 148 THEN 1 END)      FROM tbl; -- 130 ms
SELECT COUNT((amount > 148) OR NULL)                 FROM tbl; -- 130 ms
SELECT COUNT(*) FILTER (WHERE amount > 148)          FROM tbl; -- 118 ms -- !

SELECT count(*) FROM tbl WHERE amount > 148; -- without index  --  75 ms -- !!
SELECT count(*) FROM tbl WHERE amount > 148; -- with index     --   1.4 ms -- !!!

db<>小提琴here

测试2计算所有行的~33％

SELECT COUNT(NULLIF(amount > 100,FALSE))            FROM tbl; -- 140 ms
SELECT SUM((amount > 100)::int)                      FROM tbl; -- 138 ms
SELECT SUM(CASE WHEN amount > 100 THEN 1 ELSE 0 END) FROM tbl; -- 139 ms
SELECT COUNT(CASE WHEN amount > 100 THEN 1 END)      FROM tbl; -- 138 ms
SELECT COUNT(amount > 100 OR NULL)                   FROM tbl; -- 137 ms
SELECT COUNT(*) FILTER (WHERE amount > 100)          FROM tbl; -- 132 ms -- !

SELECT count(*) FROM tbl WHERE amount > 100; -- without index  -- 102 ms -- !!
SELECT count(*) FROM tbl WHERE amount > 100; -- with index     --  55 ms -- !!!

db<>小提琴here

每组中的最后一个测试使用仅索引扫描,这就是为什么它有助于计算所有行的三分之一.当涉及所有行的大约5％或更多时,普通索引或位图索引扫描不能与顺序扫描竞争.

Postgres的旧测试9.1

为了验证我在PostgreSQL 9.1.6中的真实生命表上使用EXPLAIN ANALYZE进行了快速测试.

符合条件kat_id>的184568行中的74208行50.所有查询都返回相同的结果.我依次运行10次以排除缓存效果并附加最佳结果作为注释：

SELECT SUM((kat_id > 50)::int)                      FROM log_kat; -- 438 ms
SELECT COUNT(NULLIF(kat_id > 50,FALSE))            FROM log_kat; -- 437 ms
SELECT COUNT(CASE WHEN kat_id > 50 THEN 1 END)      FROM log_kat; -- 437 ms
SELECT COUNT((kat_id > 50) OR NULL)                 FROM log_kat; -- 436 ms
SELECT SUM(CASE WHEN kat_id > 50 THEN 1 ELSE 0 END) FROM log_kat; -- 432 ms

性能几乎没有任何真正的差异.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!