Быстрое агрегирование в 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’а, когда в параллельных транзакциях удалется последний Олег и добавляется новый.
(ответ: при помощи уникального индекса и ON CONFLICT DO NOTHING)