Комментарии 21
Скажите, а чем ТАКОЙ запрос хуже???
SELECT customer_id, doc_id, dt, sum FROM doc WHERE customer_id IN (1,2,4,8,16,32,64) LIMIT 7
Если вы используете в запросе с with поле-запись, то почему бы её не использовать в первоначальном запросе?
select id, (SELECT doc FROM doc WHERE customer_id = id ORDER BY dt DESC LIMIT 1)
from unnest(ARRAY[1,2,4,8,16,32,64]) id;
Поскольку драйверы поголовно не понимают тип record, необходимо развернуть запись в столбцы (SELECT ...).* - а тогда это будет ровно первый переписанный вариант.
Можно обернуть в ещё один select
select s.*
from (
select (SELECT doc FROM doc WHERE customer_id = d.customer_id ORDER BY dt DESC LIMIT 1)
from doc d
where d.customer_id in (1,2,4,8,16,32,64)) s
сделал in чтобы убрать пустые строки
Вот как раз убирать пустые строки - противоречит задаче. Ну и двойной поиск по doc вернет на первом уровне все документы по этим клиентам - то есть далеко не 7.
Так как есть первичный ключ, понял, что можно обойтись без record. Вообще можно использовать стандартный вариант решения такой задачи
select
d.customer_id,
d.doc_id,
d.dt,
d.sum,
author.emp_name author_name,
executor.emp_name executor_name
from
doc d,
employee author,
employee executor
where d.customer_id in (1,2,4,8,16,32,64)
and d.dt = (select max(dt) from doc where doc_id = d.doc_id)
and d.emp_author = author.emp_id
and d.emp_executor = executor.emp_id
Странно, но мне для выборки последних строк по каждому клиенту ни один из Ваших вариантов в голову бы не пришёл. Я бы через оконные функции действовал:
select *
from (
select *, row_number()
over (partition by d.customer_id
order by dt desc) as rn
from ...
) preselect
where rn=1Поля, правда, перечислил бы (Извините, пишу с телефона). Интересно, как оно в смысле плана именно в postgres (до сих пор только с ms работал)?
Навскидку - чтобы отобрать только "первые" пронумерованные окном записи, их надо как-то отобрать "все", что не супербыстро.
Присоединюсь к автору комментария: через оконные функции получаются самые эффективные планы:
with a as (
select doc_id,
row_number() over (partition by customer_id order by dt desc) rn
from doc
where customer_id in (1,2,4,8,16,32,64))
select doc.*
from a
join doc on doc.doc_id = a.doc_id
where rn = 1;тут для вычисления последней записи он просканирует индекс, а уже саму запись прочитает из кучи
Хотя, да, признаю, с LATERAL быстрее
У этого запроса все-таки несколько существенных проблем (план):
он обращается к doc дважды (точнее, Bitmap + N x Index Scan)
он вычитывает все записи по этим customer_id
он не возвращает строку с id, для которого вообще нет документов
он в 1.5-2.5 раза медленнее, чем LATERAL
Вам не кажется, что лучше сделать запрос последнего документа для покупателя отдельным? Это позволит легко такой запрос закешировать, причём не только на сервере, но и даже на клиенте. Сбрасывать такой кеш не сложно при добавлении документов.
Отдельным от чего - от СУБД и SQL-запроса по клиентам?
Тут мы уже выходим за рамки БД и ступаем на зыбкую почву архитектурных модификаций системы - отдельный кэш и его поддержка в актуальном состоянии отдельная непростая тема. Зато в качестве альтернативы можно пробовать прямо в триггере создания/модификации "последнего" документа записывать его ID прямо в карточку клиента.
Но эффективность этого способа сильно зависит от контента в записи клиента - если там лежит огромный JSON с какими-то атрибутами, его постоянное перезаписывание и рост bloat могут "съесть" весь эффект от более быстрого запроса при чтении.
Поэтому я и говорю про кеш, а не денормализацию. В этом случае субд (или обёртке вокруг неё) достаточно кинуть событие, что такой-то документ изменился. А владелец кеша уже решает насколько ему это событие релевантно и сбрасывает кеш, если подозревает его неактуальность.
А дальше мы занимаемся проблемами этого кэша:
поиска недолетевшего события
организацией очередей событий
некорректно отработавшего обработчика события
периодической проверки логической целостности его данных
необходимости пересинхронизации при потере связи / проблем на узле
И не всегда оно того стоит.
Зачем? Для всего этого уже есть готовые решения. Зато мы получаем качественно иной уровень сервиса - данные обновляются в реальном времени, а не так, что смотришь на данные, а они уже 10 минут как не актуальны.
Да, решения есть - но их много и разных, что сильно усложняет архитектуру и ее сопровождение. А на другой чаше весов - "все в БД", и нельзя однозначно сказать, что это всегда хуже
.
На самом деле получение последней версии документов для массива пользователей, это очень частая задача в том или ином виде. В принципе, это постоянная задача если вы делаете версионность чего-либо
PostgreSQL Antipatterns: «где-то я тебя уже видел...»