Fix Memoize to work with partitionwise joining.

A couple of places weren't up to speed for this.  By sheer good
luck, we didn't fail but just selected a non-memoized join plan,
at least in the test case we have.  Nonetheless, it's a bug,
and I'm not quite sure that it couldn't have worse consequences
in other examples.  So back-patch to v14 where Memoize came in.

Richard Guo

Discussion: https://postgr.es/m/CAMbWs48GkNom272sfp0-WeD6_0HSR19BJ4H1c9ZKSfbVnJsvRg@mail.gmail.com

diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 2a3f0ab7bfc8f46f9d0b82db8f09b2d217abf125..cda9d4c1a8f639c74de947ea043aa37866a2c1af 100644 (file)
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -505,6 +505,7 @@ get_memoize_path(PlannerInfo *root, RelOptInfo *innerrel,
                 Path *outer_path, JoinType jointype,
                 JoinPathExtraData *extra)
 {
+   RelOptInfo *top_outerrel;
    List       *param_exprs;
    List       *hash_operators;
    ListCell   *lc;
@@ -594,10 +595,21 @@ get_memoize_path(PlannerInfo *root, RelOptInfo *innerrel,
            return NULL;
    }
 
+   /*
+    * When considering a partitionwise join, we have clauses that reference
+    * the outerrel's top parent not outerrel itself.
+    */
+   if (outerrel->reloptkind == RELOPT_OTHER_MEMBER_REL)
+       top_outerrel = find_base_rel(root, bms_singleton_member(outerrel->top_parent_relids));
+   else if (outerrel->reloptkind == RELOPT_OTHER_JOINREL)
+       top_outerrel = find_join_rel(root, outerrel->top_parent_relids);
+   else
+       top_outerrel = outerrel;
+
    /* Check if we have hash ops for each parameter to the path */
    if (paraminfo_get_equal_hashops(root,
                                    inner_path->param_info,
-                                   outerrel,
+                                   top_outerrel,
                                    innerrel,
                                    &param_exprs,
                                    &hash_operators,

diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index bc9958a1d2737c6f0b0cac0d6312bb61834fdf30..43d54e8f3dc445ab27942cbca84cf639d1d57ca5 100644 (file)
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -4193,6 +4193,7 @@ do { \
 
                FLAT_COPY_PATH(mpath, path, MemoizePath);
                REPARAMETERIZE_CHILD_PATH(mpath->subpath);
+               ADJUST_CHILD_ATTRS(mpath->param_exprs);
                new_path = (Path *) mpath;
            }
            break;

diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 540de8a4a7a60fc912309e6e9b4e0580dcd696aa..cbaefd9f043d0d9be624fc1402d4e10e23473210 100644 (file)
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1513,8 +1513,8 @@ typedef struct MemoizePath
 {
    Path        path;
    Path       *subpath;        /* outerpath to cache tuples from */
-   List       *hash_operators; /* hash operators for each key */
-   List       *param_exprs;    /* cache keys */
+   List       *hash_operators; /* OIDs of hash equality ops for cache keys */
+   List       *param_exprs;    /* expressions that are cache keys */
    bool        singlerow;      /* true if the cache entry is to be marked as
                                 * complete after caching the first record. */
    bool        binary_mode;    /* true when cache key should be compared bit

diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 00438eb1ea07a2ada626abd5738e7082c4e83e7d..de43afa76ed7653a35d5e93510c539068e8a6486 100644 (file)
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -197,6 +197,45 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false);
 (8 rows)
 
 DROP TABLE strtest;
+-- Ensure memoize works with partitionwise join
+SET enable_partitionwise_join TO on;
+CREATE TABLE prt (a int) PARTITION BY RANGE(a);
+CREATE TABLE prt_p1 PARTITION OF prt FOR VALUES FROM (0) TO (10);
+CREATE TABLE prt_p2 PARTITION OF prt FOR VALUES FROM (10) TO (20);
+INSERT INTO prt VALUES (0), (0), (0), (0);
+INSERT INTO prt VALUES (10), (10), (10), (10);
+CREATE INDEX iprt_p1_a ON prt_p1 (a);
+CREATE INDEX iprt_p2_a ON prt_p2 (a);
+ANALYZE prt;
+SELECT explain_memoize('
+SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
+                                     explain_memoize                                      
+------------------------------------------------------------------------------------------
+ Append (actual rows=32 loops=N)
+   ->  Nested Loop (actual rows=16 loops=N)
+         ->  Index Only Scan using iprt_p1_a on prt_p1 t1_1 (actual rows=4 loops=N)
+               Heap Fetches: N
+         ->  Memoize (actual rows=4 loops=N)
+               Cache Key: t1_1.a
+               Cache Mode: logical
+               Hits: 3  Misses: 1  Evictions: Zero  Overflows: 0  Memory Usage: NkB
+               ->  Index Only Scan using iprt_p1_a on prt_p1 t2_1 (actual rows=4 loops=N)
+                     Index Cond: (a = t1_1.a)
+                     Heap Fetches: N
+   ->  Nested Loop (actual rows=16 loops=N)
+         ->  Index Only Scan using iprt_p2_a on prt_p2 t1_2 (actual rows=4 loops=N)
+               Heap Fetches: N
+         ->  Memoize (actual rows=4 loops=N)
+               Cache Key: t1_2.a
+               Cache Mode: logical
+               Hits: 3  Misses: 1  Evictions: Zero  Overflows: 0  Memory Usage: NkB
+               ->  Index Only Scan using iprt_p2_a on prt_p2 t2_2 (actual rows=4 loops=N)
+                     Index Cond: (a = t1_2.a)
+                     Heap Fetches: N
+(21 rows)
+
+DROP TABLE prt;
+RESET enable_partitionwise_join;
 -- Exercise Memoize code that flushes the cache when a parameter changes which
 -- is not part of the cache key.
 -- Ensure we get a Memoize plan

diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index 0979bcdf768ca4c43a10ace0ff33eab6b57e1413..17c5b4bfab5ccd737e1117b3c6be921a0c97f825 100644 (file)
--- a/src/test/regress/sql/memoize.sql
+++ b/src/test/regress/sql/memoize.sql
@@ -104,6 +104,25 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false);
 
 DROP TABLE strtest;
 
+-- Ensure memoize works with partitionwise join
+SET enable_partitionwise_join TO on;
+
+CREATE TABLE prt (a int) PARTITION BY RANGE(a);
+CREATE TABLE prt_p1 PARTITION OF prt FOR VALUES FROM (0) TO (10);
+CREATE TABLE prt_p2 PARTITION OF prt FOR VALUES FROM (10) TO (20);
+INSERT INTO prt VALUES (0), (0), (0), (0);
+INSERT INTO prt VALUES (10), (10), (10), (10);
+CREATE INDEX iprt_p1_a ON prt_p1 (a);
+CREATE INDEX iprt_p2_a ON prt_p2 (a);
+ANALYZE prt;
+
+SELECT explain_memoize('
+SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
+
+DROP TABLE prt;
+
+RESET enable_partitionwise_join;
+
 -- Exercise Memoize code that flushes the cache when a parameter changes which
 -- is not part of the cache key.