While trying the codes of MemoizePath I noticed that MemoizePath cannot
be generated if inner path is a union-all AppendPath, even if it is
parameterized. And I found the problem is that for a union-all
AppendPath the ParamPathInfo is not created in the usual way. Instead
it is created with get_appendrel_parampathinfo, which just creates a
struct with no ppi_clauses. As a result, MemoizePath cannot find a
proper cache key to use.
This problem does not exist for AppendPath generated for a partitioned
table though, because in this case we always create a normal param_info
with ppi_clauses, for use in run-time pruning.
As an illustration, we can use the table 'prt' in sql/memoize.sql with
the settings below
set enable_hashjoin to off;
set enable_mergejoin to off;
set enable_seqscan to off;
set enable_material to off;
explain (costs off) select * from prt_p1 t1 join prt t2 on t1.a = t2.a;
QUERY PLAN
--
Nested Loop
-> Index Only Scan using iprt_p1_a on prt_p1 t1
-> Memoize
Cache Key: t1.a
Cache Mode: logical
-> Append
-> Index Only Scan using iprt_p1_a on prt_p1 t2_1
Index Cond: (a = t1.a)
-> Index Only Scan using iprt_p2_a on prt_p2 t2_2
Index Cond: (a = t1.a)
(10 rows)
explain (costs off) select * from prt_p1 t1 join (select * from prt_p1
union all select * from prt_p2) t2 on t1.a = t2.a;
QUERY PLAN
---
Nested Loop
-> Index Only Scan using iprt_p1_a on prt_p1 t1
-> Append
-> Index Only Scan using iprt_p1_a on prt_p1
Index Cond: (a = t1.a)
-> Index Only Scan using iprt_p2_a on prt_p2
Index Cond: (a = t1.a)
(7 rows)
As we can see, MemoizePath can be generated for partitioned AppendPath
but not for union-all AppendPath.
For the fix I think we can relax the check in create_append_path and
always use get_baserel_parampathinfo if the parent is a baserel.
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1266,7 +1266,7 @@ create_append_path(PlannerInfo *root,
* partition, and it's not necessary anyway in that case. Must skip it
if
* we don't have "root", too.)
*/
- if (root && rel->reloptkind == RELOPT_BASEREL &&
IS_PARTITIONED_REL(rel))
+ if (root && rel->reloptkind == RELOPT_BASEREL)
pathnode->path.param_info = get_baserel_parampathinfo(root,
rel,
required_outer);
Thanks
Richard
