Re: Вложенные SELECT и GROUP BY

Раз никто не отвечает то сделаю задачу более реальной, может я тупо
ламерствую.

Значит есть таблицы c логами (логи не совсем простые: к ним могут
приатачиваться аттачи (всякие дампы отладочные, входные запросы итп),
а так же сортировка по тегам итп):

log_tags: [ id:SERIAL, name: text ]
- метки при логах

log_titles:
[ id:SERIAL, title:text, time:timestamp, level:enum, ... куча мусора ]
- лог-сообщения

log_titles_tags: [ title_id:INT, tag_id:INT ]
- связь сообщений с метками

log_messages: [ id:SERIAL, title_id:INT, body:text, title:text ]
- аттачи при сообщениях

Показываем список с логами столбики:

Плюс фильтры по меткам и (или) по заголовку

Уровень|Время|Заголовок|Метки|мусор|количество аттачей|

Запрос:

SELECT
"log_titles".*,
"log_titles"."time"::timestamp(0) AS "time",
COUNT(DISTINCT "log_messages"."id") AS "bodies",
array_agg(DISTINCT "log_tags"."name") AS "tags"

FROM
"log_titles"

LEFT JOIN "log_messages" ON "log_messages"."title_id" = "log_titles"."id"
LEFT JOIN "log_titles_tags" ON "log_titles_tags"."title_id" = "log_titles"."id"
LEFT JOIN "log_tags" ON "log_tags"."id" = "log_titles_tags"."tag_id"

WHERE
1 = 1
% if ($filter->{tags}) {
AND "log_tags"."name" IN (<% list @{ $filter->{tags} } %>)
% }
% if ($filter->{level}) {
AND "log_titles"."level" IN (<% list @{ $filter->{level} } %>)
% }
% if ($filter->{title}) {
AND "log_titles"."title" like <%= '%' . $filter->{title} . '%' %>
% }

GROUP BY
"log_titles"."id"

ORDER BY
"log_titles"."<%== $order->{by} %>" <%== $order->{desc} %>

LIMIT
%= $pager->entries_per_page
OFFSET
%= $pager->skipped

Получается при полутора миллионах записей в таблице log_titles запрос
выполняется порядка 10 секунд. Даже при том что если фильтр ни один не стоит.

EXPLAIN показывает что основные затраты на то что он аггрегаторные
таблицы JOIN'ит с самого начала и на это немерянное количество
ресурсов уходит, хотя эти аггрегаторы нужно уже к итоговой выборке
подклеить.

Переписываем на раздельные запросы (да криво коряво, но):

WITH "tag_set" AS (
SELECT
"id",
"name"
FROM
"log_tags"
% if ($filter->{tags}) {
WHERE "name" IN (<% list @{ $filter->{tags} } %>)
% }
),
"title_set" AS (
SELECT
"log_titles".*
FROM
"log_titles"

% if ($filter->{tags}) {
LEFT JOIN "log_titles_tags"
ON "log_titles_tags"."title_id" = "log_titles"."id"
JOIN "tag_set" ON "tag_set"."id" = "log_titles_tags"."tag_id"
% }

WHERE
1 = 1
% if ($filter->{level}) {
AND "log_titles"."level" IN (<% list @{ $filter->{level} } %>)
% }
% if ($filter->{title}) {
AND "log_titles"."title" like <%= '%' . $filter->{title} . '%' %>
% }

ORDER BY
"log_titles"."<%== $order->{by} %>" <%== $order->{desc} %>

LIMIT
%= $pager->entries_per_page
OFFSET
%= $pager->skipped
)

SELECT
"log_titles".*,
COUNT(DISTINCT "log_messages"."id") AS "bodies",
array_agg("log_tags"."name") AS "tags"
FROM
"log_titles"
LEFT JOIN "log_messages" ON "log_messages"."title_id" = "log_titles"."id"
LEFT JOIN "log_titles_tags" ON "log_titles_tags"."title_id" = "log_titles"."id"
LEFT JOIN "log_tags" ON "log_titles_tags"."tag_id" = "log_tags"."id"
WHERE
"log_titles"."id" IN (SELECT "id" FROM "title_set")
GROUP BY
"log_titles"."id"
ORDER BY
"log_titles"."<%== $order->{by} %>" <%== $order->{desc} %>

Запрос начинает на тех же данных выполняться впятеро быстрее (а с
фильтрами втрое).

Вот и в самом втором SELECT можно ли выборку делать не из log_titles,
а из фейковой title_set?

Ну и все тот же вопрос: как с вложенными выборками GROUP BY делать?
или может у меня тут чушь в разбиении на сущности и надо как-то по
другому сделать?

--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537