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

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

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.

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

CREATE INDEX index_a_on_test ON test (a);

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

=> \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’е написать.

=> 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. Правда, делать тысячу запросов в базу — так себе идея. Но мы можем сделать то же самое, используя рекурсивный запрос:

=> 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’а, когда в параллельных транзакциях удалется последний Олег и добавляется новый.

Смотрите также