performance - example - postgresql COUNT(DISTINCT...) muy lento

row count postgresql

Question

Tengo una consulta SQL muy simple:

SELECT COUNT(DISTINCT x) FROM table;

Mi mesa tiene alrededor de 1.5 millones de filas. Esta consulta se ejecuta bastante lentamente; toma alrededor de 7,5 s, en comparación con

 SELECT COUNT(x) FROM table;

que toma alrededor de 435ms. ¿Hay alguna manera de cambiar mi consulta para mejorar el rendimiento? Intenté agrupar y hacer un conteo regular, así como también poner un índice en x; ambos tienen el mismo tiempo de ejecución de 7.5s.

Answer 1

Puedes usar esto:

SELECT COUNT(*) FROM (SELECT DISTINCT column_name FROM table_name) AS temp;

Esto es mucho más rápido que:

COUNT(DISTINCT column_name)

Answer 2

Si su count(distinct(x)) es significativamente más lento que count(x) , puede acelerar esta consulta manteniendo conteos de valores x en tablas diferentes, por ejemplo table_name_x_counts (x integer not null, x_count int not null) , usando triggers . Pero su rendimiento de escritura se verá afectado y si actualiza varios valores de x en una sola transacción, deberá hacerlo en un orden explícito para evitar un posible punto muerto.

Answer 3

También estaba buscando la misma respuesta, porque en algún momento necesité total_count con valores distintos junto con límite / desplazamiento .

Porque es un poco complicado de hacer: obtener el recuento total con valores distintos junto con el límite / desplazamiento. Por lo general, es difícil obtener un recuento total con límite / desplazamiento. Finalmente conseguí el camino a seguir -

SELECT DISTINCT COUNT(*) OVER() as total_count, * FROM table_name limit 2 offset 0;

El rendimiento de las consultas también es alto.

Answer 4

-- My default settings (this is basically a single-session machine, so work_mem is pretty high)
SET effective_cache_size=''2048MB'';
SET work_mem=''16MB'';
/echo original
EXPLAIN ANALYZE
SELECT
COUNT (distinct val) as aantal
FROM one
;
/echo group by+count(*)
EXPLAIN ANALYZE
SELECT
distinct val
-- , COUNT(*)
FROM one
GROUP BY val;
/echo with CTE
EXPLAIN ANALYZE
WITH agg AS (
SELECT distinct val
FROM one
GROUP BY val
)
SELECT COUNT (*) as aantal
FROM agg
;

Resultados:

original QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=36448.06..36448.07 rows=1 width=4) (actual time=1766.472..1766.472 rows=1 loops=1)
-> Seq Scan on one (cost=0.00..32698.45 rows=1499845 width=4) (actual time=31.371..185.914 rows=1499845 loops=1)
Total runtime: 1766.642 ms
(3 rows)
group by+count(*)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=36464.31..36477.31 rows=1300 width=4) (actual time=412.470..412.598 rows=1300 loops=1)
-> HashAggregate (cost=36448.06..36461.06 rows=1300 width=4) (actual time=412.066..412.203 rows=1300 loops=1)
-> Seq Scan on one (cost=0.00..32698.45 rows=1499845 width=4) (actual time=26.134..166.846 rows=1499845 loops=1)
Total runtime: 412.686 ms
(4 rows)
with CTE
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=36506.56..36506.57 rows=1 width=0) (actual time=408.239..408.239 rows=1 loops=1)
CTE agg
-> HashAggregate (cost=36464.31..36477.31 rows=1300 width=4) (actual time=407.704..407.847 rows=1300 loops=1)
-> HashAggregate (cost=36448.06..36461.06 rows=1300 width=4) (actual time=407.320..407.467 rows=1300 loops=1)
-> Seq Scan on one (cost=0.00..32698.45 rows=1499845 width=4) (actual time=24.321..165.256 rows=1499845 loops=1)
-> CTE Scan on agg (cost=0.00..26.00 rows=1300 width=0) (actual time=407.707..408.154 rows=1300 loops=1)
Total runtime: 408.300 ms
(7 rows)

El mismo plan que para el CTE también podría ser producido por otros métodos (funciones de ventana)