Блог

Я хотел бы заранее извиниться, потому что я не смог найти правильное / точное имя для этого вопроса.. complex query я имею в виду, что отношения между таблицами являются либо one to many или many to many .

В принципе, что меня интересует — если в запросе есть a join , и это соединение имеет свой собственный select оператор — достаточно ли умна база данных, чтобы выполнить весь этот оператор один раз, или она запрашивает каждый select оператор в join предложении отдельно?

Вот пример того, чего я хочу достичь. У нас есть 3 таблицы:

 CREATE TABLE persons (id int, name text, pets_ids int[]);
INSERT INTO persons VALUES (1, 'John', '{}'), (2, 'Jill', '{1,2,3}'), (3, 'Mary', '{2}');

CREATE TABLE pets (id int, name text);  
INSERT INTO pets VALUES (1,'Fluffy'),(2,'Buster'),(3,'Doggy');

CREATE TABLE personFriendhips (person_id int, person_friend_id int);
INSERT INTO personFriendhips VALUES (1, 2), (1, 3), (2, 3);
 

Я хотел бы собрать статистические данные в одном запросе для всех пользователей: идентификатор пользователя, имя, количество друзей и имена домашних животных.

Проблема в том, что нет никакой связи между таблицами friendships и pets, поэтому, если бы я выполнил 2 объединения в одном и том же операторе, я бы в итоге получил неправильное количество друзей (см. Скрипку ниже).:

 SELECT 
    per1.id
    ,per1.name
    ,count(fr1.person_friend_id) as friendsCount
    ,array_agg(pet1.name::TEXT) as petNames
FROM persons per1
    left outer join personFriendhips fr1 ON per1.id = fr1.person_id 
    left outer join pets pet1 on (ARRAY[pet1.id] <@ per1.pets_ids)
group by per1.id, per1.name, fr1.person_id
order by per1.id
;
 

Результаты

Поэтому вместо этого я должен указать запросу агрегировать имена домашних животных отдельно от остальной части инструкции:

 SELECT 
    per1.id
    ,per1.name
    ,count(fr1.person_friend_id) as friendsCount
    ,pet2.petNames
FROM persons per1
    left outer join personFriendhips fr1 ON per1.id = fr1.person_id 
    left outer join (
      SELECT 
          per2.id
          ,array_agg(pet1.name::TEXT) as petNames
      FROM persons per2
          left outer join pets pet1 on (ARRAY[pet1.id] <@ per2.pets_ids)
      group by per2.id) as pet2 on pet2.id = per1.id
group by per1.id, per1.name, fr1.person_id, pet2.petNames
order by per1.id
;
 

results

So here is my concern — If I have 1000 persons in results — does that mean that the join subquery was executed separately for each person (1000 times total), or is the db engine smart enough to recognize what I am trying to achieve and merges all joins, so that the single statement is executed? (a.k.a. first query style, but without duplicates)

you can find the fiddle here: https://www.db-fiddle.com/f/6diyQPTE5WLzgD693RZcJ3/0

I would like to make a query that is being run as a «single entity» rather than 2 entities running separately and joined together later.