PostgreSQL JPPD(Lateral View)

View Merging이 실패한 경우, 조인 조건을 뷰 내부로 밀어 넣는 방법
조인 조건을 상수화 하거나 LATERAL View를 사용한다.
PostgreSQL 9.3 이상부터 지원. JPPD를 직접 구현할 수 있는 튜닝방법
Oracle의 경우 12c부터 Lateral View를 제공

 

explain (costs false, timing false, analyze)
select a.*, b.*
from customer a,
 LATERAL (select count(*) cnt, custid, salesdate
 from sales
 where sales.custid = a.custid
 group by custid, salesdate) b
where a.custname = 'ksy90001';
------------------- QUERY PLAN ----------------------------------------------
Nested Loop (actual rows=1 loops=1)
 -> Index Scan using customer_idx02 on customer a (actual rows=1 loops=1)
 Index Cond: ((custname)::text = 'ksy90001'::text)
 -> HashAggregate (actual rows=1 loops=1)
 Group Key: sales.custid, sales.salesdate
 -> Bitmap Heap Scan on sales (actual rows=1000 loops=1)
 Recheck Cond: (custid = a.custid)
 Heap Blocks: exact=1000
 -> Bitmap Index Scan on sales_idx02 (actual rows=1000 loops=1)
 Index Cond: (custid = a.custid)
Planning time: 0.112 ms
Execution time: 1.449 ms

 

참고: https://engineering-skcc.github.io/sql/SQL_OracleOuterJoin%EC%A0%95%EB%A6%AC4/

Oracle OUTER JOIN 정리 4 Lateral View