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-09 18:52:27
Message-ID: 20111209185226.GA6116@apache.rbscorp.ru (view raw or flat)
Thread:
Lists: pgsql-ru-general
>> никак не привыкну к постгрисовым GROUP BY
>> 
>> есть выборка:
>> 
>> SELECT
>>     "t1".*,
>>     array_agg(t2.id) AS "t2_ids"
>> FROM
>>     "t1"
>> LEFT JOIN "t2" ON "t2"."t1_id" = "t1"."id"
>> 
>> GROUP BY
>>     "t1"."id"
>> 
>> LIMIT
>>     100
>> OFFSET
>>     100
>> 

> Можно спрятать ARRAY_AGG во вложенный SELECT, тогда проблемы не будет:

> SELECT t1.*, COALESCE(t2.ids, '{}')
> FROM t1
> LEFT JOIN (SELECT t2.t1_id, ARRAY_AGG(t2.id) AS ids
> FROM t2 GROUP BY 1) AS t2 ON (t1.id = t2.t1_id)
> LIMIT 100
> OFFSET 100



а на самом деле задача растет из следующей

допустим имеем несколько таблиц

t1 t2 t3

допустим t3 имеет связь много к одному к t1
а t2 имеет связь 1 к 1 к t1

и надо сделать выборку, обычную страничную вида

t1.col1, t2.col2, array_agg(t3.col3)

табличка вида например

пользователь - имя группы - список прав что он имеет

ну или другой пример придумать.

ну и вот если мы показываем такую выборку постранично

SELECT
    ...
FROM
    t1
LEFT JOIN t2 ON ..
LEFT JOIN t3 ON ..
GROUP BY t1.id
ORDER BY t1.colN

LIMIT X
OFFSET Y

с определенного размера начинает план запроса быть ужасным. и
получается что дешевле выбрать сперва X элементов из таблицы t1, со
смещением Y а уже потом JOIN'ы делать на таблицы t2 и t3. при этом
LIMIT + ORDERBY въезжает во внутренний запрос, ORDERBY остается еще и
во внешнем и общий запрос выполняется в десятки раз быстрее. с MySQL
мы использовали этот прием. тут наткнулся на то что просмотрщик логов
которые хранятся в БД выполняется очень долго - поглядел на план -
надо убрать JOIN на таблицу пользователей которые к логам привязаны и
на таблицу названий уровней. попытался так сделать - упирается в то
что после вноса в вложенный запрос проблемы с GROUP BY.

то есть по сути организуем МАЛЕНЬКУЮ выборку и к ней приджойниваем еще
кучу столбиков из разных таблиц. но вот если тут нужен 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: Dmitry E. OboukhovDate: 2011-12-13 10:52:17
Subject: Re: Вложенные SELECT и GROUP BY
Previous:From: Kirill SimonovDate: 2011-12-09 18:17:08
Subject: Re: Вложенные SELECT и GROUP BY

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