Para desempenho absoluto, SUM é mais rápido ou COUNT?

Na maioria das vezes você mesmo já respondeu a pergunta. Tenho alguns detalhes a acrescentar:

No PostgreSQL (e outros RDBMS que suportam o booleantipo) você pode usar o booleanresultado do teste diretamente. Transmita para integere SUM():

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

Ou use-o em uma NULLIF()expressão e COUNT():

COUNT(NULLIF(amount > 100, FALSE))

Ou com um simples OR NULL:

COUNT(amount > 100 OR NULL)

Ou várias outras expressões. O desempenho é quase idêntico . COUNT()é tipicamente um pouco mais rápido que SUM(). Ao contrário SUM()e como o Paul já comentou , COUNT()nunca mais volta NULL, o que pode ser conveniente. Relacionado:

• Otimização de consultas ou índices ausentes?

Desde o Postgres 9.4 também existe a FILTERcláusula agregada . Ver:

• Retornar contagens para vários intervalos em uma única instrução SELECT

É mais rápido do que todos os itens acima em cerca de 5 a 10%:

COUNT(*) FILTER (WHERE amount > 100)

Se a consulta for tão simples quanto seu caso de teste, com apenas uma única contagem e nada mais, você poderá reescrever:

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

... que é o verdadeiro rei do desempenho, mesmo sem índice.
Com um índice aplicável, pode ser mais rápido em ordens de magnitude, especialmente com varreduras somente de índice.

Referências

Postgres 13

db<>fique aqui

Basicamente os mesmos resultados do Postgres 10 abaixo. (Também adicionei um teste sem o novo paralelismo ao violino, para comparar maçãs com maçãs.)

Postgres 10

Executei uma nova série de testes para o Postgres 10, incluindo a FILTERcláusula agregada e demonstrando o papel de um índice para pequenas e grandes contagens.

Configuração simples:

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);

Os tempos reais variam um pouco devido ao ruído de fundo e às especificidades da bancada de teste. Mostrando os melhores tempos típicos de um conjunto maior de testes. Esses dois casos devem capturar a essência:

Teste 1 contando ~ 1% de todas as linhas

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<>fique aqui

Teste 2 contando ~ 33% de todas as linhas

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<>fique aqui

O último teste em cada conjunto usou uma varredura somente de índice , razão pela qual ajudou a contar um terço de todas as linhas. As varreduras de índice simples ou de bitmap não podem competir com uma varredura sequencial quando envolvem aproximadamente 5% ou mais de todas as linhas.

Teste antigo para o Postgres 9.1

Para verificar fiz um teste rápido com EXPLAIN ANALYZEuma tabela da vida real no PostgreSQL 9.1.6.

74208 de 184568 linhas qualificadas com a condição kat_id > 50. Todas as consultas retornam o mesmo resultado. Eu executei cada uma 10 vezes em turnos para excluir os efeitos de cache e anexei o melhor resultado como nota:

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

Quase nenhuma diferença real no desempenho.

Este é o meu teste no SQL Server 2012 RTM.

if object_id('tempdb..#temp1') is not null drop table #temp1;
if object_id('tempdb..#timer') is not null drop table #timer;
if object_id('tempdb..#bigtimer') is not null drop table #bigtimer;
GO

select a.*
into #temp1
from master..spt_values a
join master..spt_values b on b.type='p' and b.number < 1000;

alter table #temp1 add id int identity(10,20) primary key clustered;

create table #timer (
    id int identity primary key,
    which bit not null,
    started datetime2 not null,
    completed datetime2 not null,
);
create table #bigtimer (
    id int identity primary key,
    which bit not null,
    started datetime2 not null,
    completed datetime2 not null,
);
GO

--set ansi_warnings on;
set nocount on;
dbcc dropcleanbuffers with NO_INFOMSGS;
dbcc freeproccache with NO_INFOMSGS;
declare @bigstart datetime2;
declare @start datetime2, @dump bigint, @counter int;

set @bigstart = sysdatetime();
set @counter = 1;
while @counter <= 100
begin
    set @start = sysdatetime();
    select @dump = count(case when number < 100 then 1 end) from #temp1;
    insert #timer values (0, @start, sysdatetime());
    set @counter += 1;
end;
insert #bigtimer values (0, @bigstart, sysdatetime());
set nocount off;
GO

set nocount on;
dbcc dropcleanbuffers with NO_INFOMSGS;
dbcc freeproccache with NO_INFOMSGS;
declare @bigstart datetime2;
declare @start datetime2, @dump bigint, @counter int;

set @bigstart = sysdatetime();
set @counter = 1;
while @counter <= 100
begin
    set @start = sysdatetime();
    select @dump = SUM(case when number < 100 then 1 else 0 end) from #temp1;
    insert #timer values (1, @start, sysdatetime());
    set @counter += 1;
end;
insert #bigtimer values (1, @bigstart, sysdatetime());
set nocount off;
GO

Olhando para execuções e lotes individuais separadamente

select which, min(datediff(mcs, started, completed)), max(datediff(mcs, started, completed)),
            avg(datediff(mcs, started, completed))
from #timer group by which
select which, min(datediff(mcs, started, completed)), max(datediff(mcs, started, completed)),
            avg(datediff(mcs, started, completed))
from #bigtimer group by which

Os resultados depois de executar 5 vezes (e repetir) são bastante inconclusivos.

which                                       ** Individual
----- ----------- ----------- -----------
0     93600       187201      103927
1     93600       187201      103864

which                                       ** Batch
----- ----------- ----------- -----------
0     10108817    10545619    10398978
1     10327219    10498818    10386498

Isso mostra que há muito mais variabilidade nas condições de execução do que diferença entre a implementação, quando medida com a granularidade do timer do SQL Server. Qualquer uma das versões pode vir no topo, e a variância máxima que já obtive é de 2,5%.

No entanto, adotando uma abordagem diferente:

set showplan_text on;
GO
select SUM(case when number < 100 then 1 else 0 end) from #temp1;
select count(case when number < 100 then 1 end) from #temp1;

StmtText (SOMA)

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012] END))
       |--Stream Aggregate(DEFINE:([Expr1011]=Count(*), [Expr1012]=SUM([Expr1004])))
            |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [tempdb].[dbo].[#temp1].[number]<(100) THEN (1) ELSE (0) END))
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#temp1]))

StmtText (COUNT)

  |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1008],0)))
       |--Stream Aggregate(DEFINE:([Expr1008]=COUNT([Expr1004])))
            |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [tempdb].[dbo].[#temp1].[number]<(100) THEN (1) ELSE NULL END))
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#temp1]))

Da minha leitura, parece que a versão SUM faz um pouco mais. Ele está realizando um COUNT além de um SUM. Dito isto, COUNT(*)é diferente e deve ser mais rápido que COUNT([Expr1004])(pular NULLs, mais lógica). Um otimizador razoável perceberá que [Expr1004]na SUM([Expr1004])versão SUM é um tipo "int" e, portanto, utilizará um registro inteiro.

De qualquer forma, embora eu ainda acredite que a COUNTversão será mais rápida na maioria dos RDBMS, minha conclusão dos testes é que vou continuar SUM(.. 1.. 0..)no futuro, pelo menos para o SQL Server por nenhum outro motivo além dos ANSI WARNINGS sendo levantados ao usar COUNT.

Em Minha experiência Fazendo um rastreamento, para ambos os métodos em uma consulta de cerca de 10.000.000, notei que Count(*) usa cerca de duas vezes a CPU e roda um pouco mais rápido. mas minhas consultas estão sem filtro.

Contar(*)

CPU...........: 1828   
Execution time:  470 ms  

Soma(1)

CPU...........: 3859  
Execution time:  681 ms