Почему планировщик запросов не может преобразовать коррелированный подзапрос?

Как PostgreSQL подходит к запросу 1 + n? Я узнал, что коррелированный подзапрос может быть переписан как левое соединение:

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

в

select   f.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.film_id
order by f.title;

Бот-запросы возвращают те же результаты, но второй запрос работает лучше.

Это заставляет меня задуматься: почему планировщик запросов не может самостоятельно выполнять такие преобразования?

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

обновить производительность

Я попытался сравнить производительность следующим образом. Я выполнил 2 последовательных цикла по 100 раз по первому запросу, а затем 2 последовательных цикла по 100 раз по второму запросу. Я проигнорировал первый цикл в обоих случаях, так как считал, что цикл прогрева.

Я получаю 16 секунд для 100x первого запроса и 11 секунд для 100x второго запроса.

Объясняются следующие:

коррелированный подзапрос:

 Index Scan using idx_title on film  (cost=0.28..24949.50 rows=1000 width=51) (actual time=0.690..74.828 rows=1000 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=24.84..24.85 rows=1 width=32) (actual time=0.068..0.068 rows=1 loops=1000)
       ->  Hash Join  (cost=10.82..24.82 rows=5 width=6) (actual time=0.034..0.055 rows=5 loops=1000)
         Hash Cond: (film_actor.actor_id = actor.actor_id)
         ->  Bitmap Heap Scan on film_actor  (cost=4.32..18.26 rows=5 width=2) (actual time=0.025..0.040 rows=5 loops=1000)
               Recheck Cond: (film_id = film.film_id)
               Heap Blocks: exact=5075
               ->  Bitmap Index Scan on idx_fk_film_id  (cost=0.00..4.32 rows=5 width=0) (actual time=0.015..0.015 rows=5 loops=1000)
                 Index Cond: (film_id = film.film_id)
         ->  Hash  (cost=4.00..4.00 rows=200 width=10) (actual time=0.338..0.338 rows=200 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 17kB
               ->  Seq Scan on actor  (cost=0.00..4.00 rows=200 width=10) (actual time=0.021..0.133 rows=200 loops=1)
 Planning time: 1.277 ms
 Execution time: 75.525 ms

присоединиться:

 Sort  (cost=748.60..751.10 rows=1000 width=51) (actual time=35.865..36.060 rows=1000 loops=1)
   Sort Key: f.title
   Sort Method: quicksort  Memory: 199kB
   ->  GroupAggregate  (cost=645.31..698.78 rows=1000 width=51) (actual time=23.953..34.204 rows=1000 loops=1)
     Group Key: f.film_id
     ->  Sort  (cost=645.31..658.97 rows=5462 width=25) (actual time=23.910..25.210 rows=5465 loops=1)
           Sort Key: f.film_id
           Sort Method: quicksort  Memory: 619kB
           ->  Hash Left Join  (cost=84.00..306.25 rows=5462 width=25) (actual time=2.098..16.237 rows=5465 loops=1)
             Hash Cond: (fa.actor_id = a.actor_id)
             ->  Hash Right Join  (cost=77.50..231.03 rows=5462 width=21) (actual time=1.786..10.636 rows=5465 loops=1)
               Hash Cond: (fa.film_id = f.film_id)
               ->  Seq Scan on film_actor fa  (cost=0.00..84.62 rows=5462 width=4) (actual time=0.018..2.221 rows=5462 loops=1)
               ->  Hash  (cost=65.00..65.00 rows=1000 width=19) (actual time=1.753..1.753 rows=1000 loops=1)
                 Buckets: 1024  Batches: 1  Memory Usage: 59kB
                 ->  Seq Scan on film f  (cost=0.00..65.00 rows=1000 width=19) (actual time=0.029..0.819 rows=1000 loops=1)
             ->  Hash  (cost=4.00..4.00 rows=200 width=10) (actual time=0.286..0.286 rows=200 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 17kB
               ->  Seq Scan on actor a  (cost=0.00..4.00 rows=200 width=10) (actual time=0.016..0.114 rows=200 loops=1)
 Planning time: 1.648 ms
 Execution time: 36.599 ms
Источник

2 ответа

Решение

Это слишком много для комментария.

Перезапись вашего коррелированного подзапроса должна быть такой:

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

Что касается производительности, Scalar Correlated Subqueries просто кажется трудным для оптимизаторов, я бы не ожидал, что они будут выполнять то же или лучше, чем переписывание вручную.

Источник

Я немного удивлен, что второй работает лучше. Например, второй должен получить синтаксическую ошибку, потому что order by до group by - но я понимаю твою точку зрения.

Но ответ на ваш вопрос заключается в том, что хотя SQL является скорее описательным языком, чем процедурным языком, структура запроса может - для некоторых баз данных - влиять на план выполнения. Если вы посмотрели на объяснения, то это именно тот случай для этих двух запросов.

Более важный ответ заключается в том, что хотя запросы выглядят одинаково, они не являются семантически равными. В частности, если film.film_id не уникален, они возвращают разные ответы.

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