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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gleb Chipiga | 2011-12-16 11:47:18 | проблема с последовательностями и pg_dump |
Previous Message | Dmitry E. Oboukhov | 2011-12-09 18:52:27 | Re: Вложенные SELECT и GROUP BY |