Skip site navigation (1) Skip section navigation (2)

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

From: "Dmitry E(dot) Oboukhov" <unera(at)debian(dot)org>
To: pgsql-ru-general(at)postgresql(dot)org
Subject: Re: Вложенные SELECT и GROUP BY
Date: 2011-12-13 10:52:17
Message-ID: 20111213105216.GE6116@apache.rbscorp.ru (view raw or flat)
Thread:
Lists: pgsql-ru-general
Раз никто не отвечает то сделаю задачу более реальной, может я тупо
ламерствую.

Значит есть таблицы 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

In response to

pgsql-ru-general by date

Next:From: Gleb ChipigaDate: 2011-12-16 11:47:18
Subject: =?KOI8-R?B?0NLPwszFzcEg0yDQz9PMxcTP18HUxczYzs/T?==?KOI8-R?B?1NHNySDJIHBnX2R1bXA=?=
Previous:From: Dmitry E. OboukhovDate: 2011-12-09 18:52:27
Subject: Re: Вложенные SELECT и GROUP BY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group