Проталкивание JOIN предикатов в подзапросах с GROUP BY

«Пост-Грэс-Кью-Эл» свободная объектно-реляционная СУБД. Обмениваемся вопросами и ответами о её работе.
Ответить
AntonS
Сообщения: 86
Зарегистрирован: Пт июн 03, 2022 8:51 am

Проталкивание JOIN предикатов в подзапросах с GROUP BY

Сообщение AntonS »

Умеет ли PostgreSQL проталкивать предикаты в подзапросы с GROUP BY и если да, то как его научить?

Есть 3 таблицы:
(А) employees_big 1 млн 70 тыс строк
(Б) departments_big 27 тыс строк
(В) vip_departments 1 строка



1. В первом запросе джойнятся таблицы А > Б > В и затем GROUP BY по результатам, показывает хороший план выполнения:

Код: Выделить всё

SELECT dep.department_name, count(*) FROM hr.employees_big emp JOIN hr.departments_big dep ON emp.department_id = dep.department_id JOIN hr.vip_departments vp ON emp.department_id = vp.department_id GROUP BY dep.department_name;

Код: Выделить всё

demo=# EXPLAIN ANALYZE SELECT dep.department_name, count(*) FROM hr.employees_big emp JOIN hr.departments_big dep ON emp.department_id = dep.department_id JOIN hr.vip_departments vp ON emp.department_id = vp.department_id
demo-# GROUP BY dep.department_name;
                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=2881.98..3152.25 rows=27027 width=23) (actual time=0.294..0.321 rows=1 loops=1)
   Group Key: dep.department_name
   Batches: 1  Memory Usage: 793kB
   ->  Nested Loop  (cost=0.71..2440.52 rows=88293 width=15) (actual time=0.080..0.086 rows=2 loops=1)
         ->  Nested Loop  (cost=0.29..9.32 rows=1 width=31) (actual time=0.062..0.065 rows=1 loops=1)
               ->  Seq Scan on vip_departments vp  (cost=0.00..1.01 rows=1 width=8) (actual time=0.023..0.025 rows=2 loops=1)
               ->  Index Scan using departments_big_pkey on departments_big dep  (cost=0.29..8.30 rows=1 width=23) (actual time=0.015..0.015 rows=0 loops=2)
                     Index Cond: (department_id = vp.department_id)
         ->  Index Only Scan using emp_big_dep_id on employees_big emp  (cost=0.43..1548.27 rows=88293 width=8) (actual time=0.015..0.017 rows=2 loops=1)
               Index Cond: (department_id = dep.department_id)
               Heap Fetches: 2
 Planning Time: 0.525 ms
 Execution Time: 0.530 ms
(13 rows)
2. Второй запрос содержит подзапрос, в котором джойнтся таблицы А и Б потом GROUP BY и уже к подзапросу джойнится таблица В.

Код: Выделить всё

SELECT ed.department_name, ed.cnt FROM (SELECT dep.department_name, dep.department_id, count(*) cnt FROM hr.employees_big emp JOIN hr.departments_big dep ON emp.department_id = dep.department_id GROUP BY dep.department_name, dep.department_id) ed JOIN hr.vip_departments vp ON ed.department_id = vp.department_id;

Код: Выделить всё

demo=# EXPLAIN ANALYZE SELECT ed.department_name, ed.department_id, ed.cnt FROM
demo-# (SELECT dep.department_name, dep.department_id, count(*) cnt FROM
demo(# hr.employees_big emp JOIN
demo(# hr.departments_big dep ON emp.department_id = dep.department_id
demo(# GROUP BY dep.department_name, dep.department_id) ed JOIN hr.vip_departments vp ON ed.department_id = vp.department_id;
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=33081.07..33724.31 rows=135 width=31) (actual time=430.150..435.678 rows=1 loops=1)
   Hash Cond: (dep.department_id = vp.department_id)
   ->  Finalize HashAggregate  (cost=33080.05..33350.32 rows=27027 width=31) (actual time=430.077..435.645 rows=23 loops=1)
         Group Key: dep.department_id
         Batches: 1  Memory Usage: 793kB
         ->  Gather  (cost=27134.11..32809.78 rows=54054 width=31) (actual time=423.798..435.505 rows=47 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Partial HashAggregate  (cost=26134.11..26404.38 rows=27027 width=31) (actual time=414.970..415.119 rows=16 loops=3)
                     Group Key: dep.department_id
                     Batches: 1  Memory Usage: 793kB
                     Worker 0:  Batches: 1  Memory Usage: 793kB
                     Worker 1:  Batches: 1  Memory Usage: 793kB
                     ->  Hash Join  (cost=822.11..23904.94 rows=445834 width=23) (actual time=14.432..296.014 rows=353334 loops=3)
                           Hash Cond: (emp.department_id = dep.department_id)
                           ->  Parallel Seq Scan on employees_big emp  (cost=0.00..21912.34 rows=445834 width=8) (actual time=0.017..103.665 rows=356667 loops=3)
                           ->  Hash  (cost=484.27..484.27 rows=27027 width=23) (actual time=14.222..14.223 rows=27027 loops=3)
                                 Buckets: 32768  Batches: 1  Memory Usage: 1802kB
                                 ->  Seq Scan on departments_big dep  (cost=0.00..484.27 rows=27027 width=23) (actual time=0.028..6.362 rows=27027 loops=3)
   ->  Hash  (cost=1.01..1.01 rows=1 width=8) (actual time=0.019..0.021 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on vip_departments vp  (cost=0.00..1.01 rows=1 width=8) (actual time=0.015..0.016 rows=2 loops=1)
 Planning Time: 0.253 ms
 Execution Time: 435.984 ms
(24 rows)

Как мы видим, результаты запросов идентичны, но план выполнения второго запроса существенно хуже, идет полное сканирование Seq Scan больших таблиц.

Умеет ли PostgreSQL трансформировать подзапросы (INLINE VIEW) с GROUP BY и протаклкивать в них условия JOIN-а для получения более оптимального плана выполнения?
mar
Сообщения: 1
Зарегистрирован: Пн июн 06, 2022 10:44 am

Re: Проталкивание JOIN предикатов в подзапросах с GROUP BY

Сообщение mar »

Попробуйте во втором запросе к hr.vip_departments vp прицепить через LATERAL JOIN свой подзапрос.
AntonS
Сообщения: 86
Зарегистрирован: Пт июн 03, 2022 8:51 am

Re: Проталкивание JOIN предикатов в подзапросах с GROUP BY

Сообщение AntonS »

Попробовал прицепить подзапрос через JOIN LATERAL, но на выбор плана выполнения это не повлияло:

Код: Выделить всё

demo=# EXPLAIN ANALYZE SELECT ed.department_name, ed.department_id, ed.cnt FROM hr.vip_departments vp JOIN LATERAL (SELECT dep.department_name, dep.department_id, count(*) cnt FROM demo(# hr.employees_big emp JOIN hr.departments_big dep ON emp.department_id = dep.department_id GROUP BY dep.department_name, dep.department_id) ed ON ed.department_id = vp.department_id;
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=33081.07..33724.31 rows=135 width=31) (actual time=749.151..770.382 rows=1 loops=1)
   Hash Cond: (dep.department_id = vp.department_id)
   ->  Finalize HashAggregate  (cost=33080.05..33350.32 rows=27027 width=31) (actual time=748.966..770.289 rows=23 loops=1)
         Group Key: dep.department_id
         Batches: 1  Memory Usage: 793kB
         ->  Gather  (cost=27134.11..32809.78 rows=54054 width=31) (actual time=739.567..769.968 rows=47 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Partial HashAggregate  (cost=26134.11..26404.38 rows=27027 width=31) (actual time=711.209..711.423 rows=16 loops=3)
                     Group Key: dep.department_id
                     Batches: 1  Memory Usage: 793kB
                     Worker 0:  Batches: 1  Memory Usage: 793kB
                     Worker 1:  Batches: 1  Memory Usage: 793kB
                     ->  Hash Join  (cost=822.11..23904.94 rows=445834 width=23) (actual time=75.717..526.359 rows=353334 loops=3)
                           Hash Cond: (emp.department_id = dep.department_id)
                           ->  Parallel Seq Scan on employees_big emp  (cost=0.00..21912.34 rows=445834 width=8) (actual time=0.055..155.054 rows=356667 loops=3)
                           ->  Hash  (cost=484.27..484.27 rows=27027 width=23) (actual time=67.252..67.253 rows=27027 loops=3)
                                 Buckets: 32768  Batches: 1  Memory Usage: 1802kB
                                 ->  Seq Scan on departments_big dep  (cost=0.00..484.27 rows=27027 width=23) (actual time=0.027..9.908 rows=27027 loops=3)
   ->  Hash  (cost=1.01..1.01 rows=1 width=8) (actual time=0.067..0.069 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on vip_departments vp  (cost=0.00..1.01 rows=1 width=8) (actual time=0.062..0.063 rows=2 loops=1)
 Planning Time: 0.264 ms
 Execution Time: 772.842 ms
(24 rows)
Ответить