Skip to content

Per quanto riguarda le prestazioni assolute, è più veloce SUM o COUNT?

Se trovi un bug nel tuo codice o progetto, ricordati di testare sempre in un ambiente di test prima di caricare il codice nel progetto finale.

Soluzione:

In gran parte avete già risposto da soli alla domanda. Ho qualche bocconcino da aggiungere:

In PostgreSQL (e altri RDBMS che supportano l'opzione boolean ) è possibile utilizzare il parametro boolean del test direttamente. Lo si può fondere con il tipo integer e SUM():

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

Oppure utilizzarlo in un file NULLIF() e COUNT():

COUNT(NULLIF(amount > 100, FALSE))

O con un semplice OR NULL:

COUNT(amount > 100 OR NULL)

O con varie altre espressioni. Le prestazioni sono quasi identiche. COUNT() è in genere leggermente più veloce di SUM(). A differenza di SUM() e come Paul ha già commentato, COUNT() non restituisce mai NULL, il che può essere conveniente. Correlato:

  • Ottimizzazione della query o indici mancanti?

Da quando Postgres 9.4 c'è anche l'aggregato FILTER clausola. Vedere:

  • Restituzione di conteggi per più intervalli in una singola istruzione SELECT

E' più veloce di tutti i precedenti di circa il 5-10%:

COUNT(*) FILTER (WHERE amount > 100)

Se la query è semplice come il vostro caso di test, con un solo conteggio e nient'altro, potete riscriverla:

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

... che è il vero re delle prestazioni, anche senza indice.
Con un indice applicabile può essere più veloce di ordini di grandezza, specialmente con le scansioni solo su indice.

Parametri di riferimento

Postgres 13

db<>fiddle qui

Sostanzialmente gli stessi risultati di Postgres 10 qui sotto.
(Ho aggiunto un test senza il nuovo parallelismo).

Postgres 10

Ho eseguito una nuova serie di test per Postgres 10, compreso l'aggregato FILTER e la dimostrazione del ruolo di un indice per i conteggi piccoli e grandi.

Configurazione semplice:

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

I tempi effettivi variano leggermente a causa del rumore di fondo e delle caratteristiche del banco di prova. Mostrare tipico migliori tempi di un insieme di test più ampio. Questi due casi dovrebbero catturare l'essenza:

Test 1 conteggio di ~ 1 % di tutte le righe

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<>fiddle qui

Test 2 conteggio di ~ 33 % di tutte le righe

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<>fiddle qui

L'ultimo test di ogni serie ha utilizzato un solo indice per cui è stata utile per il conteggio di un terzo di tutte le righe. Le scansioni con indice semplice o con indice bitmap non possono competere con una scansione sequenziale quando coinvolgono circa il 5% o più di tutte le righe.

Vecchio test per Postgres 9.1

Per verificare ho eseguito un rapido test con EXPLAIN ANALYZE su una tabella reale in PostgreSQL 9.1.6.

74208 di 184568 righe qualificate con la condizione kat_id > 50. Tutte le query restituiscono lo stesso risultato. Le ho eseguite 10 volte a turno per escludere gli effetti della cache e ho aggiunto il risultato migliore come 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

Non c'è quasi nessuna differenza di prestazioni.

Questo è il mio test su 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

Esaminando separatamente le singole esecuzioni e i batch

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

I risultati dopo aver eseguito 5 volte (e ripetuto) sono piuttosto inconcludenti.

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

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

Dimostra che c'è molta più variabilità nelle condizioni di esecuzione che differenza tra le implementazioni, se misurata con la granularità del timer di SQL Server. Entrambe le versioni possono avere la meglio e la varianza massima che ho ottenuto è del 2,5%.

Tuttavia, adottando un approccio diverso:

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 (SUM)

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

Dalla mia lettura, sembra che la versione SUM faccia un po' di più. Esegue un CONTE in aggiunta a una SOMMA. Detto questo, COUNT(*) è diverso e dovrebbe essere più veloce di COUNT([Expr1004]) (salta i NULL, più logica). Un ottimizzatore ragionevole capirà che [Expr1004] in SUM([Expr1004]) nella versione SUM è un tipo "int" e quindi utilizza un registro intero.

In ogni caso, anche se continuo a credere che il COUNT sarà più veloce nella maggior parte degli RDBMS, la conclusione dei test è che sceglierò la versione SUM(.. 1.. 0..) in futuro, almeno per SQL Server, per la sola ragione che le WARNINGS ANSI vengono sollevate quando si usa COUNT.

Se ti piace il progetto, puoi lasciare un articolo su cosa aggiungeresti a questo tutorial.



Utilizzate il nostro motore di ricerca

Ricerca
Generic filters

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.