В holistic.dev есть 3 группы правил - performance, architect, security.
Часть из этих правил имеют простую природу и срабатывают на основании структуры абстрактного синтаксического дерева.
Подобным образом работают линтеры. Например, eslint для javascript.
Такие правила писать легко и быстро и за день можно сделать пяток. Но, писать их скучно :)
Например, правило, рекомендующее избегать конструкций WHERE TRUE или JOIN ON TRUE
Правила, которые основываются на знании о типах и ограничениях, возникающих в момент выполнения запроса писать намного увлекательнее. Но и времени это занимает прилично. Особенно, когда это правило касается производительности.
Сегодня я расскажу вам про правило, на которое я собирался потратить не более 30 минут, но потратил гораздо больше...
Я не очень люблю писать performance-правила. Приходится гонять много тестов в разных окружениях и подбирать ссылки на материалы, которые потом попадут в детальное описание правила. Туда же должны попасть пруфы - запросы, которые вы можете выполнить сами, чтобы убедиться в том, что рекомендация дана по делу.
Есть крайне противоположные мнения относительно рекомендаций по улучшению производительности. Одно - "Анализатор расскажет мне про отсутствующие индексы и я их сразу сделаю". Второе - "Когда начнет тормозить, мы это заметим и поправим. Преждевременная оптимизация — зло!".
Но правда, как обычно, где-то посередине.
Действующие лица: я, функция count, stackoverflow.
Скорее всего вы знаете, что функция count - опасная штука. Она перебирает все поля в таблице (seq scan), чтобы выдать точное количество строк. И это может занимать неприличное время на больших таблицах. Ускорить count можно несколькими способами, которые больше зависят от бизнес-процессов, чем от конкретного запроса.
К слову, сейчас про count описано почти 2 десятка правил и часть из них уже в проде.
Функция count имеет 2 основных применения:
- count(*) или count(любая константа) считает общее количество строк. Кстати, в postgresql count(*) работает быстрее, чем count(1)
- count (колонка или выражение) считает количество значений, отличных от NULL
Но иногда хочется посчитать не просто количество не-NULL значений, а количество уникальных значений.
Для этого можно не задумываясь использовать count(DISTINCT a). Но есть нюанс...
Такой запрос запросто будет МИНИМУМ в 10 раз медленнее.
Оно и понятно. Нужно взять все строки, найти уникальные, и только потом сосчитать их.
Что тут можно сделать? Как минимум, предупредить разработчика о таком неприятном факте, т.к. проблему с запросом он заметит скорее всего не скоро.
А можно ускорить? Вообще-то да. DISTINCT всегда работает медленнее, чем GROUP BY, поэтому, возможны ситуации, где можно сначала сгруппировать данные, а потом посчитать их количество.
Будет как-то так:
SELECT count (*) FROM (SELECT a FROM t GROUP BY a) t1
Даже можно уникальные по нескольким полям считать, не то что с DISTINCT.
Нормально, Григорий! Отлично, Константин!
Так... надо подготовить ссылки и пруфы. Окей, гугл, postgresql select disctinct...
https://stackoverflow.com/questions/11250253/postgresql-countdistinct-very-slow
Ну! А я о чем говорил?! Только тут ребята про GROUP BY не подумали... Ну да ладно, все кейсы в документации распишу. Потом...
Так, пруфы...
Делаем таблицу, заполняем, камера, мотор...
Запрос c count(DISTINCT a) - 243.630 ms
Запрос c count(*) FROM (SELECT) - 509.572 ms
ЭЭЭЭ.... wat?
А с индексом?
247.845 ms / 291.898 ms
Это как? SO не может врать! 317 votes, "holy queries batman! This sped up my PostgreSQL count distinct from 190s to 4.5 whoa!"
... пропустим пару часов экспериментов с разными версиями базы, таблицами с индексами и без ...