GROUPE PAR et COMPTER de PostgreSQL

Il est également EXISTE:

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 ncôté comme vous 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;

Le plus de lignes par post il y a dans votes, plus la différence dans les performances. Juste essayer avec explain analyze.

count(DISTINCT x) permettra de recueillir toutes les lignes, de tri ou de hachage, puis de les prendre en compte uniquement la première par identique. EXISTS ne scan votes (ou, de préférence, un index sur post_id) jusqu'à la première correspondance est trouvée.

Si chaque post_id est garanti d'être présent dans la table posts (par exemple, par la contrainte de clé étrangère), ce court formulaire est équivalente à la forme 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 avez eu des travaux dans la forme plus simple, trop:

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

---

De référence

Pour vérifier mes demandes, j'ai couru un test sur mon serveur de test avec des ressources limitées. Le tout dans un schéma distinct:

Configuration de Test

Faux typique d'un post /vote situation:

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 retourné le même résultat (8093 de 9107 postes votes).

J'ai couru 4 tests avec EXPLAIN ANALYZE (au meilleur des cinq) sur Postgres 9.1.4 avec chacun des trois requêtes et annexé le résultant total runtimes.

1. Comme est.

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 (seq scan sur les postes, analyse d'index sur les votes, boucle imbriquée)
4. 85 ms

count(DISTINCT x) au long de la forme avec une 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 courte sans adhérer

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

@wildplasser de la requête avec une CTE utilise le même plan que la forme longue (analyse d'index sur les postes, analyse d'index sur les votes, jointure de fusion), plus un peu de surcharge pour le CCE. Meilleur temps:

• 366 ms

Index-analyse uniquement dans les prochaines PostgreSQL 9.2 peut changer le résultat pour chacune de ces requêtes.

DROP SCHEMA y CASCADE;  -- clean up

---

Liés, plus détaillée de référence pour Postgres 9.5 (en fait de la récupération des lignes distinctes, et pas seulement de comptage):

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