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@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
  `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537