Grouper par et compter dans PostgreSQL

Il y a aussi EXISTS:

SELECT count(*) AS post_ct
FROM   posts p
WHERE  EXISTS (SELECT 1 FROM votes v WHERE v.post_id = p.id);

Qui, dans PostgreSQL et avec plusieurs entrées sur le côté n comme vous l'avez sans doute, est généralement plus rapide que count(DISTINCT x):

SELECT count(DISTINCT p.id) AS post_ct
FROM   posts p
JOIN   votes v ON v.post_id = p.id;

Plus il y a de lignes par message dans votes, plus la différence de performance est grande. Juste essayer avec EXPLAIN ANALYZE.

count(DISTINCT x) collectera toutes les lignes, les trie ou les hachera, puis ne considérera que le premier Par ensemble identique. {[9] } ne balayera que votes (ou, de préférence, un index sur post_id) jusqu'à ce que la première correspondance soit trouvée.

Si chaque id post_est garanti d'être présent dans la table posts (par exemple, par contrainte de clé étrangère), cette forme courte est équivalente à la forme plus longue:

SELECT count(DISTINCT post_id) AS post_ct
FROM   votes;

Cela peut effectivement être plus rapide que la première requête avec EXISTS, avec pas ou peu d'entrées par la poste.

La requête que vous aviez fonctionne aussi sous une forme plus simple:

SELECT count(*) AS post_ct
FROM  (
    SELECT 1
    FROM   posts 
    JOIN   votes ON votes.post_id = posts.id 
    GROUP  BY posts.id
    ) x;

Référence

Pour vérifier mes revendications j'ai couru un benchmark sur mon serveur de test avec des ressources limitées. Tous dans un schéma séparé:

Configuration D'essai

Faux une situation de poste / vote typique:

CREATE SCHEMA y;
SET search_path = y;

CREATE TABLE posts (
  id   int PRIMARY KEY -- I don't use "id" as column name
, post text);

INSERT INTO posts
SELECT g, repeat(chr(g%100 + 32), (random()* 500)::int) -- random text
FROM   generate_series(1,10000) g;

DELETE FROM posts WHERE random() > 0.9;  -- create ~10 % dead tuples

CREATE TABLE votes (
  vote_id serial PRIMARY KEY
, post_id int REFERENCES posts(id)
, up_down bool
);

INSERT INTO votes (post_id, up_down)
SELECT g.* 
FROM  (
   SELECT ((random()* 21)^3)::int + 1111 AS post_id -- uneven vote distribution
        , random()::int::bool AS up_down
   FROM   generate_series(1,70000)
   ) g
JOIN   posts p ON p.id = g.post_id;

Toutes les requêtes suivantes ont renvoyé le même résultat (8093 des 9107 postes avaient des votes).
J'ai exécuté 4 tests avec EXPLAIN ANALYZE (au meilleur des cinq) sur Postgres 9.1.4 avec chacune des trois requêtes et ajouté les temps d'exécution totaux résultants.

1. Comme être.

2. Après ..

   ANALYZE posts;
   ANALYZE votes;
   

3. Après ..

   CREATE INDEX foo on votes(post_id);
   

4. Après ..

   VACUUM FULL ANALYZE posts;
   CLUSTER votes using foo;
   

count(*) ... WHERE EXISTS

1. 253 ms
2. 220 ms
3. 85 ms (analyse seq sur les messages, analyse d'index sur les votes, boucle imbriquée)
4. 85 ms

count(DISTINCT x) - formulaire long avec jointure

1. 354 ms
2. 358 ms
3. 373 ms (analyse d'index sur les postes, analyse d'index sur les votes, jointure de fusion)
4. 330 ms

count(DISTINCT x) - forme abrégée sans jointure

1. 164 ms
2. 164 ms
3. 164 ms (toujours seq scan)
4. 142 ms

Meilleur moment pour requête d'origine en question:

• 353 ms

Pour version simplifiée:

• 348 ms

@la requête de wildplasser avec un CTE utilise le même plan que le formulaire long (analyse d'index sur les messages, analyse d'index sur les votes, Fusion join) plus un peu de frais généraux pour le CTE. Meilleur temps:

• 366 ms

Les analyses Index uniquement dans le prochain PostgreSQL 9.2 peuvent modifier le résultat de chacune de ces requêtes.

DROP SCHEMA y CASCADE;  -- clean up

Benchmark lié, plus détaillé pour Postgres 9.5 (récupérant en fait des lignes distinctes, pas seulement en comptant):

• sélectionnez la première ligne de chaque groupe par groupe?

WITH uniq AS (
        SELECT DISTINCT posts.id as post_id
        FROM posts
        JOIN votes ON votes.post_id = posts.id
        -- GROUP BY not needed anymore
        -- GROUP BY posts.id
        )
SELECT COUNT(*)
FROM uniq;

En utilisant OVER() et LIMIT 1:

SELECT COUNT(1) OVER()
FROM posts 
   INNER JOIN votes ON votes.post_id = posts.id 
GROUP BY posts.id
LIMIT 1;