h1. Быстрое агрегирование в PostgreSQL

Возьмем такую таблицу:

bc[sql]. CREATE table test (a integer, b integer);
INSERT INTO test (SELECT q.a, generate_series(1,10000) AS b FROM (SELECT generate_series(1,1000) AS a) q);

В ней 10 миллионов строк, но всего 1000 вариантов значений колонки @a@. И мы хотим выбрать эту тысячу вариантов значений колонки @a@.

Сделаем по этой колонке индекс — в надежде, что это нам поможет:

bc[sql]. CREATE INDEX index_a_on_test ON test (a);

И выбираем двумя самыми очевидными способами.

bc[sql]. => \timing
Timing is on.
=> SELECT a FROM test GROUP BY a;
Time: 2310.102 ms
=> SELECT DISTINCT a FROM test;
Time: 2354.694 ms

Так себе результат для выборки тысячи чиселок. Почему так долго? Да потому что эти запросы проходят по всем 10 миллионам строк в таблице, а потом агрегируют их. Все равно что @| uniq@ в bash’е написать.

bc[sql]. => EXPLAIN SELECT DISTINCT a FROM test;
                              QUERY PLAN                              
----------------------------------------------------------------------
 HashAggregate  (cost=169248.00..169258.00 rows=1000 width=4)
   Group Key: a
   ->  Seq Scan on test  (cost=0.00..144248.00 rows=10000000 width=4) 

Наличие индекса никак не помогает. Но есть одна хитрость. Если мы сделаем @SELECT a FROM test ORDER BY a LIMIT 1@ — база моментально выдаст самое маленькое значение @a@ — 1 — использовав при этом индекс. Дальше мы можем сделать @SELECT a FROM test WHERE a > 1 ORDER BY a LIMIT 1@ — и база снова моментально прыгнет по индексу и выдаст 2. Правда, делать тысячу запросов в базу — так себе идея. Но мы можем сделать то же самое, используя "рекурсивный запрос":https://www.postgresql.org/docs/current/static/queries-with.html:

bc[sql]. => WITH RECURSIVE rec AS (
  SELECT a FROM (
    SELECT a FROM test ORDER BY a LIMIT 1
  ) t1 UNION ALL (
    SELECT (
      SELECT a
      FROM test
      WHERE a > rec.a
      ORDER BY a
      LIMIT 1
    ) AS a
    FROM rec
    WHERE rec.a IS NOT NULL
    LIMIT 1
  )
)
SELECT * FROM rec
Time: 23.440 ms

Да, я тоже ненавижу рекурсивные запросы. Но иногда без черной магии не обойтись.

Мне могут возразить, что мои проблемы в этом случае из-за того, что у меня база денормализована, и я не вынес колонку @a@ в отдельную таблицу. Тогда то же самое можно бы было сделать при помощи @SELECT a FROM a_table@.

Но такой фокус пройдет только тогда, когда @a@ — это действительно отдельная сущность, а не просто поле. Представим, что в таблице @test@ мы хранили не чиселки, а сотрудников, а колонка @a@ — это организации, в которых они работают. Тогда все хорошо, но только потому, что организации не исчезают, когда мы удаляем из них сотрудников.

Другое дело — если бы мы хранили в таблице все тех же сотрудников, но в колонке @a@ были бы их имена. И мы бы хотели узнать, сколько у нас всего уникальных имен. В этом случае содержать отдельную таблицу с именами — это то еще развлечение. Попробуйте забавы ради придумать, как избежать race condition’а, когда в параллельных транзакциях удалется последний Олег и добавляется новый.

(ответ: при помощи уникального индекса и @ON CONFLICT DO NOTHING@)

h2(#see-also). Смотрите также

* "PostgreSQL, полнотекстовый поиск, ранжирование, JSON, или MongoDB не нужна":/ru/mongodb-не-нужна