postgresql COUNT(DISTINCT ...) très lent

J'ai une question très simple requête SQL:

SELECT COUNT(DISTINCT x) FROM table;

Ma table a environ 1,5 million de lignes. Cette requête est en cours d'exécution assez lentement; il faut environ 7,5 s, par rapport à

 SELECT COUNT(x) FROM table;

qui prend environ 435ms. Est-il possible de modifier ma requête pour améliorer les performances? J'ai essayé de regroupement et de pratiquer régulièrement un comte, ainsi que de mettre un index sur x; les deux ont le même 7,5 s de temps d'exécution.

Je ne le pense pas. Obtenir les valeurs distinctes de 1,5 million de lignes est juste va être lent.
Je l'ai juste essayé en C#, l'obtention de valeurs distinctes de 1,5 million de nombres entiers à partir de la mémoire prend plus d'une seconde sur mon ordinateur. Donc, je pense que vous êtes probablement hors de la chance.
Le plan de requête dépendra très largement de la structure de la table (index) et le réglage de la mise au point des constantes (de travail)mem, effective_cache_size, random_page_cost). Avec raisonnable de réglage de la requête pourrait être exécuté en moins d'une seconde.
Pourriez-vous être plus précis? Ce que l'index et le réglage des constantes seraient nécessaires pour l'obtenir à moins d'une seconde? Pour des raisons de simplicité, supposons que c'est un tableau à deux colonnes avec une clé primaire sur la première colonne y, et je suis en train de faire cette " distincte de la requête sur une deuxième colonne x de type int, avec 1,5 million de lignes.
Je suis juste à expérimenter: votre yery me coûte 1,7 s; distinct(val, count*) coûte environ 400 ms. Un CTE va sans doute aider le planificateur. BRB.
Encore une fois, pourriez-vous être précis avec ce genre de CTE aider le planificateur?
Deux pensées ... il pourrait être possible d'obtenir une approximation par la pratique "expliquer select distinct val de table" et de voir combien de lignes le planificateur de l'estime. L'autre pensée ... devrait probablement possible d'une façon ou d'une autre pour trouver le nombre de différentes entrées dans l'index lui-même. Malheureusement je n'ai pas de temps à les étudier pour le moment. Ah, troisième suggestion ... à l'aide d'un redondant stats table avec un compteur, mise à jour par un déclencheur. Aucune des suggestions sont très belle, cependant. Ayant un index, il faut vraiment être possible de faire le décompte relativement rapide...
Le CTE est plus ou moins un truc pour garder le comte+distinctes dans les différentes couches (et la cause de la "hash" plan pour être utilisé) hachés plan a besoin de quelques work_mem; réglage work_mem=64; force d'un index (ou tableau) de balayage, qui est environ deux fois plus lent. LOL, j'ai juste prouvé que posttgres est plus rapide que le C# 😉
Avec la même requête, je ne suis pas à l'aide de la "hachage" plan; je suis "unique", "groupe", "trier".
Quelle est votre version de postgres?
Mon postgres version 9.1.
S'il vous plaît, inclure la définition de la table avec tous les indices (\d sortie de psql est bonne) et précise la colonne que vous avez un problème avec. Il serait bon de voir EXPLAIN ANALYZE de deux requêtes.

InformationsquelleAutor ferson2020 | 2012-06-28

248

Vous pouvez utiliser ceci:
```
SELECT COUNT(*) FROM (SELECT DISTINCT column_name FROM table_name) AS temp;
```
C'est beaucoup plus rapide que:
```
COUNT(DISTINCT column_name)
```
- saint requêtes de batman! Ce accéléré mon postgres count distinct de 190 à 4,5 whoa!
- J'aimerais une explication des raisons pour lesquelles cela fonctionne. Bon conseil!
- J'ai trouvé ce fil de discussion sur http://www.postgresql.org qui parle de la même chose: lien. L'une des réponses (par Jeff Janes) dit que COUNT(DISTINCT()) trie le tableau pour faire son travail au lieu d'utiliser hachage.
- Puis-je vous poser une question? Depuis COUNT(DISTINCT()) effectue un tri, il sera certainement utile d'avoir un indice sur le column_name surtout avec une quantité relativement faible de work_mem (où le hachage produira relatevely grande quantité de lots). Depuis, il n'est pas toujours mauvais d'utiliser COUNT (DISTINCT()_, n'est-ce pas?
- corrigés.
- wow. merci @Maumau
- personne ne sait pourquoi ces 2 requêtes peuvent renvoyer des résultats différents si il y a un 'NULL' valeur dans column_name?
- ne compte que non, les valeurs null. count(*) compte des lignes. Donc, la première/plus longue, sont également pris en compte le nul de la ligne (une fois). Changement de count(column_name) pour les faire se comporter de la même.
InformationsquelleAutor Ankur

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

Résultats:

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)

Le même régime que pour la CCE pourrait également être produits par d'autres méthodes (fonctions de la fenêtre)

Avez-vous pris en compte l'effet de la mise en cache? Si trois d'expliquer "analyser" par la suite, le premier peut être lente de l'extraction de choses à partir du disque, tandis que les deux derniers peuvent être rapidement récupérer de la mémoire.
En effet: effective_cache_size est le premier paramètre à modifier. La mienne est de 2 go, IIRC.
J'ai mis mon effective_cache_size à 2 go, avec aucun changement dans la performance. Tous les autres paramètres vous suggère de peaufinage? Si oui, à quoi?
1) comment avez-vous mis? (avez-vous HUP-il?) 2) pensez-vous réellement avoir cette quantité de mémoire disponible? 3) montrez-nous votre plan. 4) peut-être que ma machine est plus rapide, ou de la vôtre a plus simultanée de la charge à traiter. @ferson2020: Ok
Je l'ai mis avec l'instruction: SET effective_cache_size='2 GO"; je n'ai que la quantité de mémoire disponible. J'ai essayé y compris mon plan de requête, mais elle ne rentre pas dans la boîte de commentaire.
1) quelle est votre estimation de la row_width? 2) avez-vous un index utilisable sur le distinct x) de la colonne? 3) vous pouvez mettre la requête à un autre endroit, github?) , J'ai pu monter dans la question d'origine.

InformationsquelleAutor wildplasser

2

Si votre count(distinct(x)) est significativement plus lent que count(x) alors vous pouvez accélérer cette requête par le maintien de la valeur de x qui compte dans table différente, par exemple table_name_x_counts (x integer not null, x_count int not null), à l'aide de déclencheurs. Mais vos performances en écriture vont souffrir et si vous mettez à jour plusieurs x valeurs de transaction unique, alors vous devrez pour ce faire, dans certains explicite afin d'éviter d'éventuels blocages.

InformationsquelleAutor Tometzky
0

J'étais aussi à la recherche même réponse, car à un certain moment, j'avais besoin de total_count avec des valeurs distinctes avec limit/offset.

Parce qu'il est peu difficile à faire - Pour obtenir le total de compter avec des valeurs distinctes avec limit/offset. Habituellement, il est difficile d'obtenir le nombre total avec limit/offset. J'ai finalement trouvé le moyen de le faire -

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

Les performances de la requête est également élevé.

InformationsquelleAutor Rana Pratap Singh

Vous devez vous connecter pour publier un commentaire.