PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение

В нашем сервисе анализа планов запросов к PostgreSQL иногда встречаются примеры запросов, которые сложно назвать эффективными. Рассмотрим один из таких запросов, который был вызван простой бизнес-задачей, и покажем, как отказ от использования агрегатных функций может значительно ускорить его выполнение. Задача заключается в поиске даты последнего документа для определённой выборки клиентов, у которых имеется несколько таких документов. Предположим, что в базе данных имеются две таблицы: одна с информацией о клиентах, а другая — с документами, относящимися к этим клиентам. Таблицы выглядят следующим образом: ``` CREATE TABLE cli( id integer PRIMARY KEY, name text); CREATE TABLE doc( cli integer, dt date); CREATE INDEX ON doc(cli, dt); ``` Foreign Keys в данной модели не используются, так как они не играют значимой роли. Однако индекс по клиенту и дате будет необходим для работы с хронологическими данными. Для отладки таблиц можно заполнить их данными: 10 тысяч клиентов и 1 миллион документов. При анализе списка клиентов можно обнаружить, что «имена» в таблице выглядят странно. Это связано с генерацией случайных данных. Если убрать из запроса поле id, результат может удивить: все «имена» станут одинаковыми. Объяснение этого явления можно найти в лекции об анализе планов из видеокурса «PostgreSQL для начинающих». Предположим, что необходимо решить задачу для клиентов, чьё «имя» начинается с символа «!». Для этого потребуется создать подходящий индекс: ``` CREATE INDEX ON cli(name text_pattern_ops); ``` Теперь можно написать запрос, который будет искать дату последнего документа для клиентов с «именем», начинающимся на «!», и имеющих несколько документов. Запрос будет выглядеть следующим образом: ``` SELECT cli.id, max(doc.dt) dt FROM cli JOIN doc ON doc.cli = cli.id WHERE cli.name LIKE '!%' GROUP BY cli.id HAVING count(*) > 1; ``` Ответ будет получен достаточно быстро — за 4,5 миллисекунды. Однако при более детальном анализе можно обнаружить, что по каждому из найденных клиентов считывалось в среднем по 101 документу. Это привело к появлению Nested Loop с 9465 записями. Но ведь требовалось всего лишь выдать дату последнего документа, если их несколько!