Пробую свои силы в типе данных JSONB в первый раз (обсуждение продолжено с (Join таблицы, использующие значение внутри столбца JSONB) по совету @Erwin, начиная новый поток)
Две таблицы (запутанные данные и имена таблиц):
1. Discussion table { discussion_id int, contact_id, group_id, discussion_updates jsonb } [has around 600 thousand rows]
2. Authorization table {user_id varchar , auth_contacts jsonb, auth_groups jsonb} [has around 100 thousand rows]
auth_contacts jsonb data has key value pairs data (as example)
- {"CC1": "rr", "CC2": "ro" }
auth_groups jsonb data has key value pairs data (as example)
- {"GRP1": "rr", "GRP2": "ro" }
1- Во-первых, при вставке в базу данных через Java JDBC: я делаю следующее:
JSONObject authContacts = new JSONObject();
for(each record in data){
authContacts.put(contactKey, contactRight);
authGroups.put(groupKey, groupRight);
}
String insertSql = "INSERT INTO SSTA_AuthAll(employee_id, auth_contacts, auth_groups) VALUES(?,?::jsonb,?::jsonb)";
//---Connect to Db and prepare query
preparedStatement.setObject(2, authContacts.toJSONString());
preparedStatement.setObject(3, authGroups.toJSONString());
//INSERT into DB
Теперь toJSONString() требует времени (иногда до 1 секунды - TIME FOR toJSON STRING LOOP: 17238 мс), что снова неэффективно. Итак, опять же, это правильный способ сделать это? В большинстве примеров в Google есть строка, которую они вставляют.
Если я напрямую вставлю MAP в jsonb coolumn , он ожидает расширение HSTORE, которое мне не следует использовать, если я собираюсь использовать jsonb?
2- Теперь о следующей части: мне нужно присоединиться к contact_id из таблицы обсуждения с contact_id типа данных auth_contacts json [который является ключевым, как показано в примере выше] и присоединиться к group_id группы auth_groups с group_id таблицы обсуждения
На данный момент пробовал присоединиться только к contact_id:
SELECT *
FROM discussion d
JOIN
(SELECT user_id, jsonb_object_keys(a.contacts) AS contacts FROM auth_contacts a WHERE user_id='XXX') AS c
ON (d.contact_id = c.contacts::text)
ORDER BY d.updated_date DESC
Это присоединение для пользователя, у которого около 60 тысяч авторизованных контактов, занимает около 60 мс, а последовательное выполнение выполняется меньше. План запутанного объяснения выглядит следующим образом:
"Sort (cost=4194.02..4198.39 rows=1745 width=301) (actual time=50.791..51.042 rows=5590 loops=1)"
" Sort Key: d.updated_date"
" Sort Method: quicksort Memory: 3061kB"
" Buffers: shared hit=11601"
" -> Nested Loop (cost=0.84..4100.06 rows=1745 width=301) (actual time=0.481..44.437 rows=5590 loops=1)"
" Buffers: shared hit=11598"
" -> Index Scan using auth_contacts_pkey on auth_contacts a (cost=0.42..8.93 rows=100 width=888) (actual time=0.437..1.074 rows=1987 loops=1)"
" Index Cond: ((user_id)::text = '105037'::text)"
" Buffers: shared hit=25"
" -> Index Scan using discussion_contact_id on discussion d (cost=0.42..40.73 rows=17 width=310) (actual time=0.016..0.020 rows=3 loops=1987)"
" Index Cond: ((contact_id)::text = (jsonb_object_keys(a.contacts)))"
" Buffers: shared hit=11573"
"Planning time: 17.866 ms"
"Execution time: 52.192 ms"
Моя конечная цель — дополнительное соединение в том же запросе с group_id. Что делает jsonb_object_keys, так это фактически создает сопоставление идентификатора пользователя и authcontacts для каждого ключа. Таким образом, для пользователя с 60 тысячами контактов будет создано представление из 60 тысяч строк (вероятно, в памяти). Теперь, если я включу соединение с auth_groups (что для примера пользователя с 60 тысячами контактов будет иметь около 1000 тысяч групп, что замедлит запрос.
Итак, это правильный способ присоединения к объекту jsonb и есть ли лучший способ сделать это?
