GROUP BY и COUNT в PostgreSQL

Запрос:

SELECT COUNT(*) as count_all, 
       posts.id as post_id 
FROM posts 
  INNER JOIN votes ON votes.post_id = posts.id 
GROUP BY posts.id;

Возвращает n записи в Postgresql:

 count_all | post_id
-----------+---------
 1         | 6
 3         | 4
 3         | 5
 3         | 1
 1         | 9
 1         | 10
(6 rows)

Я просто хочу получить количество возвращенных записей: 6.

Я использовал подзапрос для достижения того, что хочу, но это не кажется оптимальным:

SELECT COUNT(*) FROM (
    SELECT COUNT(*) as count_all, posts.id as post_id 
    FROM posts 
    INNER JOIN votes ON votes.post_id = posts.id 
    GROUP BY posts.id
) as x;

Как мне получить количество записей в этом контексте прямо в PostgreSQL?

Ответы

Ответ 1

Думаю, вам просто нужно COUNT(DISTINCT post_id) FROM votes.

См. раздел "4.2.7. Совокупные выражения" в http://www.postgresql.org/docs/current/static/sql-expressions.html.

EDIT: Исправлена ​​моя неосторожная ошибка за комментарий Эрвина.

Ответ 2

Существует также EXISTS:

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

В Postgres и с несколькими записями на n-стороне, как вы, вероятно, имеете, обычно это быстрее, чем count(DISTINCT post_id):

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

Чем больше строк в посте в votes, тем больше разница в производительности. Тест с EXPLAIN ANALYZE.

count(DISTINCT post_id) должен прочитать все строки, отсортировать или хэшировать их, а затем учитывать только первый для каждого идентичного набора. EXISTS будет сканировать только votes (или, предпочтительно, индекс на post_id), пока не будет найдено первое совпадение.

Если каждый post_id в votes гарантированно присутствует в таблице posts (ссылочная целостность обеспечивается с помощью ограничения внешнего ключа), эта краткая форма эквивалентна более длинной форме:

SELECT count(DISTINCT post_id) AS post_ct
FROM   votes;

На самом деле может быть быстрее, чем запрос EXISTS, если в каждом сообщении нет или мало записей.

Ваш запрос также работает в более простой форме:

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

Benchmark

Чтобы проверить свои утверждения, я провел тест на своем тестовом сервере с ограниченными ресурсами. Все в отдельной схеме:

Тестовая настройка

Поддельная типичная ситуация с постом/голосованием:

CREATE SCHEMA y;
SET search_path = y;

CREATE TABLE posts (
  id   int PRIMARY KEY
, 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 distribution
        , random()::int::bool AS up_down
   FROM   generate_series(1,70000)
   ) g
JOIN   posts p ON p.id = g.post_id;

Все следующие запросы дали одинаковый результат (8093 из 9107 сообщений имели голоса).
Я провел 4 теста с EXPLAIN ANALYZE ant, взяв лучший из пяти на Postgres 9.1.4 с каждым из трех запросов, и добавил итоговое общее время выполнения.

  1. Как есть.

  2. После.. 

    ANALYZE posts;
ANALYZE votes;

  3. После..

    CREATE INDEX foo on votes(post_id);

  4. После..

    VACUUM FULL ANALYZE posts;
CLUSTER votes using foo;

count(*) ... WHERE EXISTS

  1. 253 мс
  2. 220 мс
  3. 85 мс - победитель (последующее сканирование постов, индексное сканирование голосов, вложенный цикл)
  4. 85 мс

count(DISTINCT x) - длинная форма с соединением

  1. 354 мс
  2. 358 мс
  3. 373 мс - (индексное сканирование постов, индексное сканирование голосов, объединение слиянием)
  4. 330 мс

count(DISTINCT x) - короткая форма без объединения

  1. 164 мс
  2. 164 мс
  3. 164 мс - (всегда последующее сканирование)
  4. 142 мс

Лучшее время для исходного запроса в вопросе:

  • 353 мс

Для упрощенной версии:

  • 348 мс

В запросе @wildplasser с CTE используется тот же план, что и у длинной формы (сканирование индекса по сообщениям, сканирование индекса по голосам, объединение слиянием), а также небольшие накладные расходы для CTE. Лучшее время:

  • 366 мс

Сканирование только по индексу в предстоящем PostgreSQL 9.2 может улучшить результат для каждого из этих запросов, прежде всего для EXISTS.

Связанный, более подробный бенчмарк для Postgres 9.5 (фактически извлекающий отдельные строки, а не просто счет):

Ответ 3

Используя OVER() и LIMIT 1:

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

Ответ 4

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;