Te doy la bienvenida a nuestro espacio, en este sitio hallarás la solucíon que buscas.
Solución:
La mayor parte del tiempo ya respondió usted mismo a la pregunta. Tengo algunos bocados que agregar:
En PostgreSQL (y otros RDBMS que admiten la boolean tipo) puede utilizar el boolean resultado de la prueba directamente. Enviarlo a integer y SUM():
SUM((amount > 100)::int))
O utilícelo en un NULLIF() expresión y COUNT():
COUNT(NULLIF(amount > 100, FALSE))
O con un simple OR NULL:
COUNT(amount > 100 OR NULL)
O varias otras expresiones. El rendimiento es casi idéntico. COUNT() suele ser un poco más rápido que SUM(). diferente a SUM() y como ya comentó Paul, COUNT() nunca regresa NULL, que puede ser conveniente. Relacionado:
- ¿Optimización de consultas o índices faltantes?
Ya que Postgres 9.4 también está el agregado FILTER cláusula. Ver:
- Devuelve recuentos para múltiples rangos en una sola instrucción SELECT
Es más rápido que todo lo anterior en alrededor del 5 – 10%:
COUNT(*) FILTER (WHERE amount > 100)
Si la consulta es tan simple como su caso de prueba, con un solo recuento y nada más, puede reescribir:
SELECT count(*) FROM tbl WHERE amount > 100;
… Cuál es el true rey del rendimiento, incluso sin índice.
Con un índice aplicable, puede ser más rápido en órdenes de magnitud, especialmente con escaneos de solo índice.
Benchmarks
Postgres 13
db <> violín aquí
Básicamente, los mismos resultados que para Postgres 10 a continuación. (Agregué una prueba sin el nuevo paralelismo).
Postgres 10
Ejecuté una nueva serie de pruebas para Postgres 10, incluido el agregado FILTER cláusula y demostrando el papel de un índice para cuentas pequeñas y grandes.
Configuración sencilla:
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);
Los tiempos reales varían bastante debido al ruido de fondo y a las características específicas del banco de pruebas. Demostración típico mejores tiempos de un conjunto mayor de pruebas. Estos dos casos deberían capturar la esencia:
Prueba 1 contando ~ 1% de todas las filas
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 <> violín aquí
Prueba 2 contando ~ 33% de todas las filas
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 <> violín aquí
La última prueba de cada conjunto utilizó un solo índice escanear, por lo que ayudó a contar un tercio de todas las filas. Los escaneos de índice simple o de mapa de bits no pueden competir con un escaneo secuencial cuando involucran aproximadamente el 5% o más de todas las filas.
Prueba anterior para Postgres 9.1
Para verificar, realicé una prueba rápida con EXPLAIN ANALYZE en una tabla de la vida real en PostgreSQL 9.1.6.
74208 de 184568 filas calificadas con la condición kat_id > 50. Todas las consultas devuelven el mismo resultado. Ejecuté cada uno como 10 veces por turnos para excluir los efectos de almacenamiento en caché y agregué el mejor 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
Casi ninguna diferencia real en el rendimiento.
Esta es mi prueba en 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
Analizar series y lotes individuales por separado
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
Los resultados después de ejecutar 5 veces (y repetir) no son concluyentes.
which ** Individual
----- ----------- ----------- -----------
0 93600 187201 103927
1 93600 187201 103864
which ** Batch
----- ----------- ----------- -----------
0 10108817 10545619 10398978
1 10327219 10498818 10386498
Muestra que hay mucha más variabilidad en las condiciones de ejecución que diferencia entre la implementación, cuando se mide con la granularidad del temporizador de SQL Server. Cualquiera de las dos versiones puede llegar a la cima, y la variación máxima que he obtenido es del 2,5%.
Sin embargo, adoptando un enfoque 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 (SUMA)
|--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]))
De mi lectura, parecería que la versión SUM hace un poco más. Está realizando un COUNT además de una SUM. Una vez dicho esto, COUNT(*) es diferente y debería ser más rápido que COUNT([Expr1004]) (omitir NULL, más lógica). Un optimizador razonable se dará cuenta de que [Expr1004] en SUM([Expr1004]) en la versión SUM es un tipo "int" y, por lo tanto, utiliza un registro de enteros.
En cualquier caso, mientras yo sigo creyendo COUNT La versión será más rápida en la mayoría de RDBMS, mi conclusión de las pruebas es que voy a ir con SUM(.. 1.. 0..) en el futuro, al menos para SQL Server sin otra razón que las ADVERTENCIAS ANSI que se generan al usar COUNT.
Si guardas alguna vacilación y forma de avanzar nuestro división te proponemos escribir una disquisición y con placer lo estudiaremos.