Как PostgreSQL подходит к запросу 1 + n?

Я тестирую базу данных Sakila, см. http://www.postgresqltutorial.com/postgresql-sample-database/. Эта база данных содержит три отношения:

  • film: film_id, название
  • actor: actor_id, first_name
  • film_actor: film_id, actor_id

Я хочу перечислить все фильмы и для каждого фильма, я хочу перечислить всех актеров, играющих в этом конкретном фильме. Я закончил со следующим запросом:

select   film_id, title, array
         (
           select     first_name
           from       actor
           inner join film_actor
           on         actor.actor_id = film_actor.actor_id
           where      film_actor.film_id = film.film_id
         ) as actors
from     film
order by title;

Концептуально это запрос 1 + n:

one query: get films
n queries: for each film f
             f.actors = array(get actors playing in f)

Я всегда понимал, что нужно избегать 1 + n запросов любой ценой, так как это плохо масштабируется.

Так что это заставило меня задуматься: как PostgreSQL реализует это внутренне? Допустим, у нас есть 1000 фильмов, выполняет ли это внутренне 1000 select actor.first_name from actor inner join ... запросы? Или PostgreSQL умнее в этом и делает что-то вроде следующего?

1. one query:  get films
2. one query:  get actors related to these films while keeping reference to film_id
3. internally: for each film f
                 f.actors = array(subset of (2) according to film_id)

Это делает 1 + 1 запросов.

Источник

2 ответа

Решение

Вы думаете во вложенных циклах. Это то, что вы должны преодолеть при работе с реляционной базой данных (если вы не используете MySQL).

То, что вы описываете как "1 + n", является вложенным циклом: вы сканируете одну таблицу, и для каждой найденной строки вы сканируете другую таблицу.

Как написан ваш SQL-запрос, у PostgreSQL нет другого выбора, кроме как выполнить вложенный цикл.

Это хорошо, пока внешний стол (film в вашем примере) имеет несколько строк. Производительность быстро ухудшается, когда внешний стол становится больше.

В дополнение к вложенным циклам, PostgreSQL имеет две другие стратегии соединения:

  • Хеш-соединение: сканируется внутреннее отношение и создается хеш-структура, где хеш-ключ является ключом соединения. Затем сканируется внешнее отношение и проверяется хеш для каждой найденной строки.

    Думайте об этом как о хеш-соединении, но с внутренней стороны у вас есть эффективная структура данных в памяти.

  • Объединение слиянием: обе таблицы сортируются по ключу объединения и объединяются путем одновременного сканирования результатов.

Рекомендуется написать запрос без "коррелированных подзапросов", чтобы PostgreSQL мог выбрать оптимальную стратегию объединения:

SELECT film_id, f.title, array_agg(a.first_name)
FROM film f
   LEFT JOIN film_actor fa USING (film_id)
   LEFT JOIN actor a USING (actor_id)
GROUP BY f.title
ORDER BY f.title;

Левое внешнее соединение используется для получения результата, даже если в фильме нет актеров.

Источник

Это, возможно, больше подходит для комментария, но это слишком долго.

Хотя я следую логике вашего запроса, я предпочитаю выражать его следующим образом:

select f.film_id, f.title,
       (select array_agg(a.first_name)
        from actor a inner join
             film_actor fa
             on a.actor_id = fa.actor_id
        where fa.film_id = f.film_id
       ) as actors
from film f
order by f.title;

Явный array_agg() разъясняет логику. Вы агрегируете подзапрос, объединяете результаты в виде массива и затем включаете его в качестве столбца во внешний запрос.

Источник
Другие вопросы по тегам