If we intend to generate a memoize node atop a path, we need some kind of cache key. Currently we search the path's parameterized clauses and its parent's lateral_vars for that. ISTM this is not sufficient because their might be lateral references derived from PlaceHolderVars, which can also act as cache key but we neglect to take into consideration. As an example, consider
create table t(a int); insert into t values (1), (1), (1), (1); analyze t; explain (costs off) select * from t t1 left join lateral (select t1.a as t1a, t2.a as t2a from t t2) s on true where s.t1a = s.t2a; QUERY PLAN ---------------------------- Nested Loop -> Seq Scan on t t1 -> Seq Scan on t t2 Filter: (t1.a = a) (4 rows) We cannot find available cache keys for memoize node because the inner side has neither parameterized path clauses nor lateral_vars. However if we are able to look in the PHV for lateral references, we will find the cache key 't1.a'. Actually we do have checked PHVs for lateral references, earlier in create_lateral_join_info. But that time we only marked lateral_relids and direct_lateral_relids, without remembering the lateral expressions. So I'm wondering whether we can fix that by fetching Vars (or PHVs) of lateral references within PlaceHolderVars and remembering them in the baserel's lateral_vars. Attach a draft patch to show my thoughts. Thanks Richard
v1-0001-Check-lateral-references-within-PHVs-for-memoize-.patch
Description: Binary data