Производительность запросов при соединении с json_agg в postgres

у меня две таблицы

Таблица записей


 id    | val1 | val2
-------+------+------+
 ent_1 | xxx  | xxx
 ent_2 | xyy  | yyy
 ent_3 | zxz  | zzz
 ent_4 | zxz  | zzz

Таблица entries_list


 id  | entry_id | val1 | val2
-----+----------+------+-------
   1 |   ent_1  | xxx  | xxx
   2 |   ent_1  | xyy  | yyy
   3 |   ent_2  | zxz  | zzz
   4 |   ent_2  | zxz  | zzz

entries_list.entry_id – это чужой ключ от entries.id.

Поэтому мне нужно найти entries, у которых есть соответствующие ссылки entry_list. Мне не нужен entries, на который нет ссылки в entry_list, и результат, который я ожидаю от этого,

[{
    id: ent_1,
    entries: [{
        id: 1,
        val1: xxx,
        val2: xxx
    }, {
        id: 1,
        val1: xxx,
        val2: xxx
    }]
}, {
    id: ent_2,
    entries: [{
        id: 3,
        val1: xxx,
        val2: xxx
    }, {
        id: 4,
        val1: xxx,
        val2: xxx
    }]
}]

из-за желаемого результата и структуры я решил использовать Json_agg и Json_build_object запрос выглядит так

SELECT entries.id, 
       Json_agg(Json_build_object('id', list.id, 'val1', list.val2, 'val2', 
       list.val2)) AS sub_list 
FROM   entries 
       INNER JOIN (SELECT id,val1,val2 
                   FROM   entries_list) AS list 
               ON entries.id = list.entry_id
GROUP  BY entries.id 
ORDER  BY entries.id

Но он выглядит очень плохо, почти 10 секунд для 1M записей. Итак, как лучше это изменить?

Я думаю о том, чтобы получить данные по плану и сгруппировать их в коде вне sql, но как запрос должен быть изменен в обоих подходах?

У меня nodejs бэкэнд и pg модуль в качестве коннектора.


postgresql sql performance query-performance postgresql-performance
person Sarath    schedule 02.08.2017    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Как работает эта версия?

SELECT e.id, 
       (SELECT Json_agg(Json_build_object('id', el.id, 'val1', el.val2, 'val2', 
       el.val2))
        FROM entries_list el 
        WHERE el.entry_id = e.id
       ) as sub_list 
FROM entries e 
ORDER BY e.id ;

Для производительности вам нужен индекс entries_list(entry_id, id, val2). Первый ключ особенно важен.

person Gordon Linoff    schedule 02.08.2017

arrow_upward
1
arrow_downward

Вы можете использовать exists вместо обычного соединения:

select 
    entry_id, 
    json_agg(json_build_object('id', id, 'val1', val2, 'val2', val2)) as sub_list 
from entries_list
where exists (
    select 1 
    from entries e 
    where entry_id = e.id)
group by entry_id 
order by entry_id;

Вам нужны индексы на entries_list(entry_id) и (очевидно, это, вероятно, первичный ключ) на entries(id).

person klin    schedule 02.08.2017