Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fla2l-0002Tg-J3 for pgsql-hackers@arkaria.postgresql.org; Fri, 03 Aug 2018 13:23:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fla2j-0007am-NZ for pgsql-hackers@arkaria.postgresql.org; Fri, 03 Aug 2018 13:22:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1flZzD-0004Wm-Qn for pgsql-hackers@lists.postgresql.org; Fri, 03 Aug 2018 13:19:19 +0000 Received: from tama50.ecl.ntt.co.jp ([129.60.39.147]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1flZz6-00052A-0v for pgsql-hackers@postgresql.org; Fri, 03 Aug 2018 13:19:18 +0000 Received: from vc2.ecl.ntt.co.jp (vc2.ecl.ntt.co.jp [129.60.86.154]) by tama50.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id w73DJ0GT010881; Fri, 3 Aug 2018 22:19:00 +0900 Received: from vc2.ecl.ntt.co.jp (localhost [127.0.0.1]) by vc2.ecl.ntt.co.jp (Postfix) with ESMTP id B48D663990A; Fri, 3 Aug 2018 22:19:00 +0900 (JST) Received: from jcms-pop21.ecl.ntt.co.jp (jcms-pop21.ecl.ntt.co.jp [129.60.87.134]) by vc2.ecl.ntt.co.jp (Postfix) with ESMTP id A8B5F639909; Fri, 3 Aug 2018 22:19:00 +0900 (JST) Received: from [IPv6:::1] (unknown [129.60.241.75]) by jcms-pop21.ecl.ntt.co.jp (Postfix) with ESMTPSA id 9E8B14005EA; Fri, 3 Aug 2018 22:19:00 +0900 (JST) Message-ID: <5B64562A.9000404@lab.ntt.co.jp> Date: Fri, 03 Aug 2018 22:18:34 +0900 From: Etsuro Fujita User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20120327 Thunderbird/11.0.1 MIME-Version: 1.0 Subject: Re: Expression errors with "FOR UPDATE" and postgres_fdw with partition wise join enabled. References: <5AFC0865.8050802@lab.ntt.co.jp> <5B1E5071.1000704@lab.ntt.co.jp> <5B23A964.9020800@lab.ntt.co.jp> <5B28FB19.3090809@lab.ntt.co.jp> <20180703163519.tsigyic5xefunv74@alap3.anarazel.de> <5B3C2A4E.5030901@lab.ntt.co.jp> <5B4F2BE9.3000304@lab.ntt.co.jp> <5B55886F.5090906@lab.ntt.co.jp> <5B5712C8.9030404@lab.ntt.co.jp> <5B59BA55.30200@lab.ntt.co.jp> <5B619D23.4010000@lab.ntt.co.jp> <5B62E8E3.80604! 02@lab.ntt.co.jp> <5358.1533241519@sss.pgh.pa.us> In-Reply-To: <5358.1533241519@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------080508030800090607080009" X-CC-Mail-RelayStamp: 1 To: Tom Lane Cc: Robert Haas , Andres Freund , Ashutosh Bapat , Rajkumar Raghuwanshi , pgsql-hackers X-TM-AS-MML: disable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------080508030800090607080009 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit (2018/08/03 5:25), Tom Lane wrote: > What I'm thinking might be a more appropriate thing, at least for > getting v11 out the door, is to refuse to generate partitionwise > joins when any whole-row vars are involved. Agreed. > (Perhaps that's not > enough to dodge all the problems, though?) > > Now, that's a bit of a problem for postgres_fdw, because it seems to > insist on injecting WRVs even when the query text does not require any. > Why is that, and can't we get rid of it? It's horrid for performance > even without any of these other considerations. But if we fail to get > rid of that in time for v11, it would mean that postgres_fdw can't > participate in PWJ, which isn't great but I think we could live with it > for now. Sorry, I don't understand this. Could you elaborate on that a bit more? > BTW, what exactly do we think the production status of PWJ is, anyway? > I notice that upthread it was stated that enable_partitionwise_join > defaults to off, as indeed it still does, and we'd turn it on later > when we'd gotten rid of some memory-hogging problems. If that hasn't > happened yet (and I don't see any open item about considering enabling > PWJ by default for v11), then I have exactly no hesitation about > lobotomizing PWJ as hard as we need to to mask this problem for v11. That hasn't happened yet; I think we left that for PG12, IIRC. Here is a patch for refusing to generate PWJ paths when WRVs are involved: * We no longer need to handle WRVs, so I've simplified build_joinrel_tlist() and setrefs.c to what they were before partition-wise join went in, as in the previous patch. * attr_needed data for each child is used for building child-joins' tlists, but I think we can build those by applying adjust_appendrel_attrs to the parent's tlists, without attr_needed. So, I've also removed that as in the previous patch. Maybe I'm missing something, though. Best regards, Etsuro Fujita --------------080508030800090607080009 Content-Type: text/x-diff; name="refuse-pwj-when-wrvs-involved.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="refuse-pwj-when-wrvs-involved.patch" *** a/contrib/postgres_fdw/expected/postgres_fdw.out --- b/contrib/postgres_fdw/expected/postgres_fdw.out *************** *** 8337,8344 **** ALTER TABLE fprt2_p1 SET (autovacuum_enabled = 'false'); ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false'); INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i; INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i; ! CREATE FOREIGN TABLE ftprt2_p1 PARTITION OF fprt2 FOR VALUES FROM (0) TO (250) SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true'); CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500) SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true'); ANALYZE fprt2; --- 8337,8345 ---- ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false'); INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i; INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i; ! CREATE FOREIGN TABLE ftprt2_p1 (b int, c varchar, a int) SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true'); + ALTER TABLE fprt2 ATTACH PARTITION ftprt2_p1 FOR VALUES FROM (0) TO (250); CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500) SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true'); ANALYZE fprt2; *************** *** 8389,8416 **** SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) 8 | | (5 rows) ! -- with whole-row reference EXPLAIN (COSTS OFF) ! SELECT t1,t2 FROM fprt1 t1 JOIN fprt2 t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a % 25 =0 ORDER BY 1,2; ! QUERY PLAN ! --------------------------------------------------------------------------------- Sort Sort Key: ((t1.*)::fprt1), ((t2.*)::fprt2) ! -> Append ! -> Foreign Scan ! Relations: (public.ftprt1_p1 t1) INNER JOIN (public.ftprt2_p1 t2) ! -> Foreign Scan ! Relations: (public.ftprt1_p2 t1) INNER JOIN (public.ftprt2_p2 t2) ! (7 rows) ! SELECT t1,t2 FROM fprt1 t1 JOIN fprt2 t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a % 25 =0 ORDER BY 1,2; ! t1 | t2 ----------------+---------------- (0,0,0000) | (0,0,0000) (150,150,0003) | (150,150,0003) (250,250,0005) | (250,250,0005) (400,400,0008) | (400,400,0008) ! (4 rows) -- join with lateral reference EXPLAIN (COSTS OFF) --- 8390,8431 ---- 8 | | (5 rows) ! -- with whole-row reference; partitionwise join does not apply EXPLAIN (COSTS OFF) ! SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2; ! QUERY PLAN ! -------------------------------------------------------- Sort Sort Key: ((t1.*)::fprt1), ((t2.*)::fprt2) ! -> Hash Full Join ! Hash Cond: (t1.a = t2.b) ! -> Append ! -> Foreign Scan on ftprt1_p1 t1 ! -> Foreign Scan on ftprt1_p2 t1_1 ! -> Hash ! -> Append ! -> Foreign Scan on ftprt2_p1 t2 ! -> Foreign Scan on ftprt2_p2 t2_1 ! (11 rows) ! SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2; ! wr | wr ----------------+---------------- (0,0,0000) | (0,0,0000) + (50,50,0001) | + (100,100,0002) | (150,150,0003) | (150,150,0003) + (200,200,0004) | (250,250,0005) | (250,250,0005) + (300,300,0006) | + (350,350,0007) | (400,400,0008) | (400,400,0008) ! (450,450,0009) | ! | (75,75,0001) ! | (225,225,0004) ! | (325,325,0006) ! | (475,475,0009) ! (14 rows) -- join with lateral reference EXPLAIN (COSTS OFF) *************** *** 8474,8479 **** SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE --- 8489,8522 ---- | | 475 | t2_phv (14 rows) + -- test FOR UPDATE; partitionwise join does not apply + EXPLAIN (COSTS OFF) + SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1; + QUERY PLAN + -------------------------------------------------------------- + LockRows + -> Sort + Sort Key: t1.a + -> Hash Join + Hash Cond: (t2.b = t1.a) + -> Append + -> Foreign Scan on ftprt2_p1 t2 + -> Foreign Scan on ftprt2_p2 t2_1 + -> Hash + -> Append + -> Foreign Scan on ftprt1_p1 t1 + -> Foreign Scan on ftprt1_p2 t1_1 + (12 rows) + + SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1; + a | b + -----+----- + 0 | 0 + 150 | 150 + 250 | 250 + 400 | 400 + (4 rows) + RESET enable_partitionwise_join; -- =================================================================== -- test partitionwise aggregates *** a/contrib/postgres_fdw/sql/postgres_fdw.sql --- b/contrib/postgres_fdw/sql/postgres_fdw.sql *************** *** 2263,2270 **** ALTER TABLE fprt2_p1 SET (autovacuum_enabled = 'false'); ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false'); INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i; INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i; ! CREATE FOREIGN TABLE ftprt2_p1 PARTITION OF fprt2 FOR VALUES FROM (0) TO (250) SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true'); CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500) SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true'); ANALYZE fprt2; --- 2263,2271 ---- ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false'); INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i; INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i; ! CREATE FOREIGN TABLE ftprt2_p1 (b int, c varchar, a int) SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true'); + ALTER TABLE fprt2 ATTACH PARTITION ftprt2_p1 FOR VALUES FROM (0) TO (250); CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500) SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true'); ANALYZE fprt2; *************** *** 2281,2290 **** EXPLAIN (COSTS OFF) SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3; SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3; ! -- with whole-row reference EXPLAIN (COSTS OFF) ! SELECT t1,t2 FROM fprt1 t1 JOIN fprt2 t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a % 25 =0 ORDER BY 1,2; ! SELECT t1,t2 FROM fprt1 t1 JOIN fprt2 t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a % 25 =0 ORDER BY 1,2; -- join with lateral reference EXPLAIN (COSTS OFF) --- 2282,2291 ---- SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3; SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3; ! -- with whole-row reference; partitionwise join does not apply EXPLAIN (COSTS OFF) ! SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2; ! SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2; -- join with lateral reference EXPLAIN (COSTS OFF) *************** *** 2296,2301 **** EXPLAIN (COSTS OFF) --- 2297,2307 ---- SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE a % 25 = 0) t1 FULL JOIN (SELECT 't2_phv' phv, * FROM fprt2 WHERE b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b; SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE a % 25 = 0) t1 FULL JOIN (SELECT 't2_phv' phv, * FROM fprt2 WHERE b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b; + -- test FOR UPDATE; partitionwise join does not apply + EXPLAIN (COSTS OFF) + SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1; + SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1; + RESET enable_partitionwise_join; *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** *** 2359,2364 **** _outRelOptInfo(StringInfo str, const RelOptInfo *node) --- 2359,2365 ---- WRITE_UINT_FIELD(baserestrict_min_security); WRITE_NODE_FIELD(joininfo); WRITE_BOOL_FIELD(has_eclass_joins); + WRITE_BOOL_FIELD(consider_partitionwise_join); WRITE_BITMAPSET_FIELD(top_parent_relids); WRITE_NODE_FIELD(partitioned_child_rels); } *** a/src/backend/optimizer/path/allpaths.c --- b/src/backend/optimizer/path/allpaths.c *************** *** 910,915 **** set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, --- 910,926 ---- } /* + * If this is a partitioned baserel, set the consider_partitionwise_join + * flag. Currently, we only consider partitionwise joins with the baserel + * if its targetlist doesn't contain a whole-row Var. + */ + if (enable_partitionwise_join && + rel->reloptkind == RELOPT_BASEREL && + rte->relkind == RELKIND_PARTITIONED_TABLE && + rel->attr_needed[InvalidAttrNumber - rel->min_attr] == NULL) + rel->consider_partitionwise_join = true; + + /* * Initialize to compute size estimates for whole append relation. * * We handle width estimates by weighting the widths of different child *************** *** 956,1013 **** set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, childrel = find_base_rel(root, childRTindex); Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL); - if (rel->part_scheme) - { - AttrNumber attno; - - /* - * We need attr_needed data for building targetlist of a join - * relation representing join between matching partitions for - * partitionwise join. A given attribute of a child will be needed - * in the same highest joinrel where the corresponding attribute - * of parent is needed. Hence it suffices to use the same Relids - * set for parent and child. - */ - for (attno = rel->min_attr; attno <= rel->max_attr; attno++) - { - int index = attno - rel->min_attr; - Relids attr_needed = rel->attr_needed[index]; - - /* System attributes do not need translation. */ - if (attno <= 0) - { - Assert(rel->min_attr == childrel->min_attr); - childrel->attr_needed[index] = attr_needed; - } - else - { - Var *var = list_nth_node(Var, - appinfo->translated_vars, - attno - 1); - int child_index; - - /* - * Ignore any column dropped from the parent. - * Corresponding Var won't have any translation. It won't - * have attr_needed information, since it can not be - * referenced in the query. - */ - if (var == NULL) - { - Assert(attr_needed == NULL); - continue; - } - - child_index = var->varattno - childrel->min_attr; - childrel->attr_needed[child_index] = attr_needed; - } - } - } - /* ! * Copy/Modify targetlist. Even if this child is deemed empty, we need ! * its targetlist in case it falls on nullable side in a child-join ! * because of partitionwise join. * * NB: the resulting childrel->reltarget->exprs may contain arbitrary * expressions, which otherwise would not occur in a rel's targetlist. --- 967,979 ---- childrel = find_base_rel(root, childRTindex); Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL); /* ! * We have to copy the parent's targetlist to the child, with ! * appropriate substitution of variables. ! * ! * Note: when considering partitionwise joining, we need the child's ! * targetlist in case it falls on nullable side in a child join even ! * if the child is deemed empty. * * NB: the resulting childrel->reltarget->exprs may contain arbitrary * expressions, which otherwise would not occur in a rel's targetlist. *************** *** 1181,1186 **** set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, --- 1147,1168 ---- 1, &appinfo); /* + * Note: we could compute appropriate attr_needed data for the child's + * variables, by transforming the parent's attr_needed through the + * translated_vars mapping. However, currently there's no need + * because attr_needed is only examined for base relations not + * otherrels. So we just leave the child's attr_needed empty. + */ + + /* + * If we consider partitionwise joins with the parent rel, do the same + * for the partitioned child rel. + */ + if (rel->consider_partitionwise_join && + childRTE->relkind == RELKIND_PARTITIONED_TABLE) + childrel->consider_partitionwise_join = true; + + /* * If parallelism is allowable for this query in general, see whether * it's allowable for this childrel in particular. But if we've * already decided the appendrel is not parallel-safe as a whole, *************** *** 3539,3546 **** generate_partitionwise_join_paths(PlannerInfo *root, RelOptInfo *rel) if (!IS_JOIN_REL(rel)) return; ! /* We've nothing to do if the relation is not partitioned. */ ! if (!IS_PARTITIONED_REL(rel)) return; /* Guard against stack overflow due to overly deep partition hierarchy. */ --- 3521,3531 ---- if (!IS_JOIN_REL(rel)) return; ! /* ! * We've nothing to do if the relation is not partitioned, or ! * partitionwise joins are not allowed for the join relation. ! */ ! if (!IS_PARTITIONED_REL(rel) || !rel->consider_partitionwise_join) return; /* Guard against stack overflow due to overly deep partition hierarchy. */ *** a/src/backend/optimizer/path/joinrels.c --- b/src/backend/optimizer/path/joinrels.c *************** *** 1318,1325 **** try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, /* Guard against stack overflow due to overly deep partition hierarchy. */ check_stack_depth(); ! /* Nothing to do, if the join relation is not partitioned. */ ! if (!IS_PARTITIONED_REL(joinrel)) return; /* --- 1318,1328 ---- /* Guard against stack overflow due to overly deep partition hierarchy. */ check_stack_depth(); ! /* ! * Nothing to do, if the join relation is not partitioned, or ! * partitionwise joins are not allowed for the join relation. ! */ ! if (!IS_PARTITIONED_REL(joinrel) || !joinrel->consider_partitionwise_join) return; /* *************** *** 1351,1356 **** try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, --- 1354,1365 ---- joinrel->part_scheme->parttypbyval, joinrel->boundinfo, rel2->boundinfo)); + /* + * Partitionwise joins should have been allowed for the joining relations. + */ + Assert(rel1->consider_partitionwise_join && + rel2->consider_partitionwise_join); + nparts = joinrel->nparts; /* *** a/src/backend/optimizer/plan/planner.c --- b/src/backend/optimizer/plan/planner.c *************** *** 3960,3965 **** create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, --- 3960,3967 ---- */ if (extra->patype != PARTITIONWISE_AGGREGATE_NONE && input_rel->part_scheme && input_rel->part_rels && + (input_rel->reloptkind == RELOPT_BASEREL || + input_rel->consider_partitionwise_join) && !IS_DUMMY_REL(input_rel)) { /* *************** *** 6913,6919 **** apply_scanjoin_target_to_paths(PlannerInfo *root, * projection-capable, that might save a separate Result node, and it also * is important for partitionwise aggregate. */ ! if (rel->part_scheme && rel->part_rels) { int partition_idx; List *live_children = NIL; --- 6915,6923 ---- * projection-capable, that might save a separate Result node, and it also * is important for partitionwise aggregate. */ ! if (rel->part_scheme && rel->part_rels && ! (rel->reloptkind == RELOPT_BASEREL || ! rel->consider_partitionwise_join)) { int partition_idx; List *live_children = NIL; *** a/src/backend/optimizer/plan/setrefs.c --- b/src/backend/optimizer/plan/setrefs.c *************** *** 41,49 **** typedef struct int num_vars; /* number of plain Var tlist entries */ bool has_ph_vars; /* are there PlaceHolderVar entries? */ bool has_non_vars; /* are there other entries? */ - bool has_conv_whole_rows; /* are there ConvertRowtypeExpr - * entries encapsulating a whole-row - * Var? */ tlist_vinfo vars[FLEXIBLE_ARRAY_MEMBER]; /* has num_vars entries */ } indexed_tlist; --- 41,46 ---- *************** *** 143,149 **** static List *set_returning_clause_references(PlannerInfo *root, int rtoffset); static bool extract_query_dependencies_walker(Node *node, PlannerInfo *context); - static bool is_converted_whole_row_reference(Node *node); /***************************************************************************** * --- 140,145 ---- *************** *** 1997,2003 **** build_tlist_index(List *tlist) itlist->tlist = tlist; itlist->has_ph_vars = false; itlist->has_non_vars = false; - itlist->has_conv_whole_rows = false; /* Find the Vars and fill in the index array */ vinfo = itlist->vars; --- 1993,1998 ---- *************** *** 2016,2023 **** build_tlist_index(List *tlist) } else if (tle->expr && IsA(tle->expr, PlaceHolderVar)) itlist->has_ph_vars = true; - else if (is_converted_whole_row_reference((Node *) tle->expr)) - itlist->has_conv_whole_rows = true; else itlist->has_non_vars = true; } --- 2011,2016 ---- *************** *** 2033,2042 **** build_tlist_index(List *tlist) * This is like build_tlist_index, but we only index tlist entries that * are Vars belonging to some rel other than the one specified. We will set * has_ph_vars (allowing PlaceHolderVars to be matched), but not has_non_vars ! * (so nothing other than Vars and PlaceHolderVars can be matched). In case of ! * DML, where this function will be used, returning lists from child relations ! * will be appended similar to a simple append relation. That does not require ! * fixing ConvertRowtypeExpr references. So, those are not considered here. */ static indexed_tlist * build_tlist_index_other_vars(List *tlist, Index ignore_rel) --- 2026,2032 ---- * This is like build_tlist_index, but we only index tlist entries that * are Vars belonging to some rel other than the one specified. We will set * has_ph_vars (allowing PlaceHolderVars to be matched), but not has_non_vars ! * (so nothing other than Vars and PlaceHolderVars can be matched). */ static indexed_tlist * build_tlist_index_other_vars(List *tlist, Index ignore_rel) *************** *** 2053,2059 **** build_tlist_index_other_vars(List *tlist, Index ignore_rel) itlist->tlist = tlist; itlist->has_ph_vars = false; itlist->has_non_vars = false; - itlist->has_conv_whole_rows = false; /* Find the desired Vars and fill in the index array */ vinfo = itlist->vars; --- 2043,2048 ---- *************** *** 2256,2262 **** static Node * fix_join_expr_mutator(Node *node, fix_join_expr_context *context) { Var *newvar; - bool converted_whole_row; if (node == NULL) return NULL; --- 2245,2250 ---- *************** *** 2326,2337 **** fix_join_expr_mutator(Node *node, fix_join_expr_context *context) } if (IsA(node, Param)) return fix_param_node(context->root, (Param *) node); - /* Try matching more complex expressions too, if tlists have any */ ! converted_whole_row = is_converted_whole_row_reference(node); ! if (context->outer_itlist && ! (context->outer_itlist->has_non_vars || ! (context->outer_itlist->has_conv_whole_rows && converted_whole_row))) { newvar = search_indexed_tlist_for_non_var((Expr *) node, context->outer_itlist, --- 2314,2321 ---- } if (IsA(node, Param)) return fix_param_node(context->root, (Param *) node); /* Try matching more complex expressions too, if tlists have any */ ! if (context->outer_itlist && context->outer_itlist->has_non_vars) { newvar = search_indexed_tlist_for_non_var((Expr *) node, context->outer_itlist, *************** *** 2339,2347 **** fix_join_expr_mutator(Node *node, fix_join_expr_context *context) if (newvar) return (Node *) newvar; } ! if (context->inner_itlist && ! (context->inner_itlist->has_non_vars || ! (context->inner_itlist->has_conv_whole_rows && converted_whole_row))) { newvar = search_indexed_tlist_for_non_var((Expr *) node, context->inner_itlist, --- 2323,2329 ---- if (newvar) return (Node *) newvar; } ! if (context->inner_itlist && context->inner_itlist->has_non_vars) { newvar = search_indexed_tlist_for_non_var((Expr *) node, context->inner_itlist, *************** *** 2461,2469 **** fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context) /* If no match, just fall through to process it normally */ } /* Try matching more complex expressions too, if tlist has any */ ! if (context->subplan_itlist->has_non_vars || ! (context->subplan_itlist->has_conv_whole_rows && ! is_converted_whole_row_reference(node))) { newvar = search_indexed_tlist_for_non_var((Expr *) node, context->subplan_itlist, --- 2443,2449 ---- /* If no match, just fall through to process it normally */ } /* Try matching more complex expressions too, if tlist has any */ ! if (context->subplan_itlist->has_non_vars) { newvar = search_indexed_tlist_for_non_var((Expr *) node, context->subplan_itlist, *************** *** 2670,2702 **** extract_query_dependencies_walker(Node *node, PlannerInfo *context) return expression_tree_walker(node, extract_query_dependencies_walker, (void *) context); } - - /* - * is_converted_whole_row_reference - * If the given node is a ConvertRowtypeExpr encapsulating a whole-row - * reference as implicit cast, return true. Otherwise return false. - */ - static bool - is_converted_whole_row_reference(Node *node) - { - ConvertRowtypeExpr *convexpr; - - if (!node || !IsA(node, ConvertRowtypeExpr)) - return false; - - /* Traverse nested ConvertRowtypeExpr's. */ - convexpr = castNode(ConvertRowtypeExpr, node); - while (convexpr->convertformat == COERCE_IMPLICIT_CAST && - IsA(convexpr->arg, ConvertRowtypeExpr)) - convexpr = castNode(ConvertRowtypeExpr, convexpr->arg); - - if (IsA(convexpr->arg, Var)) - { - Var *var = castNode(Var, convexpr->arg); - - if (var->varattno == 0) - return true; - } - - return false; - } --- 2650,2652 ---- *** a/src/backend/optimizer/util/placeholder.c --- b/src/backend/optimizer/util/placeholder.c *************** *** 20,26 **** #include "optimizer/pathnode.h" #include "optimizer/placeholder.h" #include "optimizer/planmain.h" - #include "optimizer/prep.h" #include "optimizer/var.h" #include "utils/lsyscache.h" --- 20,25 ---- *************** *** 415,424 **** add_placeholders_to_joinrel(PlannerInfo *root, RelOptInfo *joinrel, Relids relids = joinrel->relids; ListCell *lc; - /* This function is called only on the parent relations. */ - Assert(!IS_OTHER_REL(joinrel) && !IS_OTHER_REL(outer_rel) && - !IS_OTHER_REL(inner_rel)); - foreach(lc, root->placeholder_list) { PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(lc); --- 414,419 ---- *************** *** 464,519 **** add_placeholders_to_joinrel(PlannerInfo *root, RelOptInfo *joinrel, } } } - - /* - * add_placeholders_to_child_joinrel - * Translate the PHVs in parent's targetlist and add them to the child's - * targetlist. Also adjust the cost - */ - void - add_placeholders_to_child_joinrel(PlannerInfo *root, RelOptInfo *childrel, - RelOptInfo *parentrel) - { - ListCell *lc; - AppendRelInfo **appinfos; - int nappinfos; - - Assert(IS_JOIN_REL(childrel) && IS_JOIN_REL(parentrel)); - Assert(IS_OTHER_REL(childrel)); - - /* Nothing to do if no PHVs. */ - if (root->placeholder_list == NIL) - return; - - appinfos = find_appinfos_by_relids(root, childrel->relids, &nappinfos); - foreach(lc, parentrel->reltarget->exprs) - { - PlaceHolderVar *phv = lfirst(lc); - - if (IsA(phv, PlaceHolderVar)) - { - /* - * In case the placeholder Var refers to any of the parent - * relations, translate it to refer to the corresponding child. - */ - if (bms_overlap(phv->phrels, parentrel->relids) && - childrel->reloptkind == RELOPT_OTHER_JOINREL) - { - phv = (PlaceHolderVar *) adjust_appendrel_attrs(root, - (Node *) phv, - nappinfos, - appinfos); - } - - childrel->reltarget->exprs = lappend(childrel->reltarget->exprs, - phv); - } - } - - /* Adjust the cost and width of child targetlist. */ - childrel->reltarget->cost.startup = parentrel->reltarget->cost.startup; - childrel->reltarget->cost.per_tuple = parentrel->reltarget->cost.per_tuple; - childrel->reltarget->width = parentrel->reltarget->width; - - pfree(appinfos); - } --- 459,461 ---- *** a/src/backend/optimizer/util/relnode.c --- b/src/backend/optimizer/util/relnode.c *************** *** 57,62 **** static void add_join_rel(PlannerInfo *root, RelOptInfo *joinrel); --- 57,67 ---- static void build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel, RelOptInfo *inner_rel, List *restrictlist, JoinType jointype); + static void build_child_join_reltarget(PlannerInfo *root, + RelOptInfo *parentrel, + RelOptInfo *childrel, + int nappinfos, + AppendRelInfo **appinfos); /* *************** *** 188,193 **** build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent) --- 193,199 ---- rel->baserestrict_min_security = UINT_MAX; rel->joininfo = NIL; rel->has_eclass_joins = false; + rel->consider_partitionwise_join = false; /* might get changed later */ rel->part_scheme = NULL; rel->nparts = 0; rel->boundinfo = NULL; *************** *** 602,607 **** build_join_rel(PlannerInfo *root, --- 608,614 ---- joinrel->baserestrict_min_security = UINT_MAX; joinrel->joininfo = NIL; joinrel->has_eclass_joins = false; + joinrel->consider_partitionwise_join = false; /* might get changed later */ joinrel->top_parent_relids = NULL; joinrel->part_scheme = NULL; joinrel->nparts = 0; *************** *** 660,665 **** build_join_rel(PlannerInfo *root, --- 667,678 ---- build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist, sjinfo->jointype); + /* Set the consider_partitionwise_join flag. */ + if (IS_PARTITIONED_REL(joinrel) && + outer_rel->consider_partitionwise_join && + inner_rel->consider_partitionwise_join) + joinrel->consider_partitionwise_join = true; + /* * Set estimates of the joinrel's size. */ *************** *** 732,737 **** build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel, --- 745,753 ---- /* Only joins between "other" relations land here. */ Assert(IS_OTHER_REL(outer_rel) && IS_OTHER_REL(inner_rel)); + /* Partitionwise joins should have been allowed for the parent joinrel. */ + Assert(parent_joinrel->consider_partitionwise_join); + joinrel->reloptkind = RELOPT_OTHER_JOINREL; joinrel->relids = bms_union(outer_rel->relids, inner_rel->relids); joinrel->rows = 0; *************** *** 773,778 **** build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel, --- 789,795 ---- joinrel->baserestrictcost.per_tuple = 0; joinrel->joininfo = NIL; joinrel->has_eclass_joins = false; + joinrel->consider_partitionwise_join = false; /* might get changed later */ joinrel->top_parent_relids = NULL; joinrel->part_scheme = NULL; joinrel->nparts = 0; *************** *** 789,802 **** build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel, /* Compute information relevant to foreign relations. */ set_foreign_rel_properties(joinrel, outer_rel, inner_rel); ! /* Build targetlist */ ! build_joinrel_tlist(root, joinrel, outer_rel); ! build_joinrel_tlist(root, joinrel, inner_rel); ! /* Add placeholder variables. */ ! add_placeholders_to_child_joinrel(root, joinrel, parent_joinrel); /* Construct joininfo list. */ - appinfos = find_appinfos_by_relids(root, joinrel->relids, &nappinfos); joinrel->joininfo = (List *) adjust_appendrel_attrs(root, (Node *) parent_joinrel->joininfo, nappinfos, --- 806,818 ---- /* Compute information relevant to foreign relations. */ set_foreign_rel_properties(joinrel, outer_rel, inner_rel); ! appinfos = find_appinfos_by_relids(root, joinrel->relids, &nappinfos); ! ! /* Set up reltarget struct */ ! build_child_join_reltarget(root, parent_joinrel, joinrel, ! nappinfos, appinfos); /* Construct joininfo list. */ joinrel->joininfo = (List *) adjust_appendrel_attrs(root, (Node *) parent_joinrel->joininfo, nappinfos, *************** *** 823,832 **** build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel, build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist, jointype); /* Child joinrel is parallel safe if parent is parallel safe. */ joinrel->consider_parallel = parent_joinrel->consider_parallel; - /* Set estimates of the child-joinrel's size. */ set_joinrel_size_estimates(root, joinrel, outer_rel, inner_rel, sjinfo, restrictlist); --- 839,851 ---- build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist, jointype); + /* If so, consider partitionwise joins for that join. */ + if (IS_PARTITIONED_REL(joinrel)) + joinrel->consider_partitionwise_join = true; + /* Child joinrel is parallel safe if parent is parallel safe. */ joinrel->consider_parallel = parent_joinrel->consider_parallel; /* Set estimates of the child-joinrel's size. */ set_joinrel_size_estimates(root, joinrel, outer_rel, inner_rel, sjinfo, restrictlist); *************** *** 895,909 **** static void build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel, RelOptInfo *input_rel) { ! Relids relids; ListCell *vars; - /* attrs_needed refers to parent relids and not those of a child. */ - if (joinrel->top_parent_relids) - relids = joinrel->top_parent_relids; - else - relids = joinrel->relids; - foreach(vars, input_rel->reltarget->exprs) { Var *var = (Var *) lfirst(vars); --- 914,922 ---- build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel, RelOptInfo *input_rel) { ! Relids relids = joinrel->relids; ListCell *vars; foreach(vars, input_rel->reltarget->exprs) { Var *var = (Var *) lfirst(vars); *************** *** 919,972 **** build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel, /* * Otherwise, anything in a baserel or joinrel targetlist ought to be ! * a Var. Children of a partitioned table may have ConvertRowtypeExpr ! * translating whole-row Var of a child to that of the parent. ! * Children of an inherited table or subquery child rels can not ! * directly participate in a join, so other kinds of nodes here. */ ! if (IsA(var, Var)) ! { ! baserel = find_base_rel(root, var->varno); ! ndx = var->varattno - baserel->min_attr; ! } ! else if (IsA(var, ConvertRowtypeExpr)) ! { ! ConvertRowtypeExpr *child_expr = (ConvertRowtypeExpr *) var; ! Var *childvar = (Var *) child_expr->arg; ! ! /* ! * Child's whole-row references are converted to look like those ! * of parent using ConvertRowtypeExpr. There can be as many ! * ConvertRowtypeExpr decorations as the depth of partition tree. ! * The argument to the deepest ConvertRowtypeExpr is expected to ! * be a whole-row reference of the child. ! */ ! while (IsA(childvar, ConvertRowtypeExpr)) ! { ! child_expr = (ConvertRowtypeExpr *) childvar; ! childvar = (Var *) child_expr->arg; ! } ! Assert(IsA(childvar, Var) &&childvar->varattno == 0); ! ! baserel = find_base_rel(root, childvar->varno); ! ndx = 0 - baserel->min_attr; ! } ! else elog(ERROR, "unexpected node type in rel targetlist: %d", (int) nodeTag(var)); ! /* Is the target expression still needed above this joinrel? */ if (bms_nonempty_difference(baserel->attr_needed[ndx], relids)) { /* Yup, add it to the output */ joinrel->reltarget->exprs = lappend(joinrel->reltarget->exprs, var); ! ! /* ! * Vars have cost zero, so no need to adjust reltarget->cost. Even ! * if it's a ConvertRowtypeExpr, it will be computed only for the ! * base relation, costing nothing for a join. ! */ joinrel->reltarget->width += baserel->attr_widths[ndx]; } } --- 932,954 ---- /* * Otherwise, anything in a baserel or joinrel targetlist ought to be ! * a Var. (More general cases can only appear in appendrel child ! * rels, which will never be seen here.) */ ! if (!IsA(var, Var)) elog(ERROR, "unexpected node type in rel targetlist: %d", (int) nodeTag(var)); + /* Get the Var's original base rel */ + baserel = find_base_rel(root, var->varno); ! /* Is it still needed above this joinrel? */ ! ndx = var->varattno - baserel->min_attr; if (bms_nonempty_difference(baserel->attr_needed[ndx], relids)) { /* Yup, add it to the output */ joinrel->reltarget->exprs = lappend(joinrel->reltarget->exprs, var); ! /* Vars have cost zero, so no need to adjust reltarget->cost */ joinrel->reltarget->width += baserel->attr_widths[ndx]; } } *************** *** 1768,1770 **** build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel, --- 1750,1775 ---- joinrel->nullable_partexprs[cnt] = nullable_partexpr; } } + + /* + * build_child_join_reltarget + * Set up a child-join relation's reltarget from a parent-join relation. + */ + static void + build_child_join_reltarget(PlannerInfo *root, + RelOptInfo *parentrel, + RelOptInfo *childrel, + int nappinfos, + AppendRelInfo **appinfos) + { + /* Build the targetlist */ + childrel->reltarget->exprs = (List *) + adjust_appendrel_attrs(root, + (Node *) parentrel->reltarget->exprs, + nappinfos, appinfos); + + /* Set the cost and width fields */ + childrel->reltarget->cost.startup = parentrel->reltarget->cost.startup; + childrel->reltarget->cost.per_tuple = parentrel->reltarget->cost.per_tuple; + childrel->reltarget->width = parentrel->reltarget->width; + } *** a/src/include/nodes/relation.h --- b/src/include/nodes/relation.h *************** *** 687,694 **** typedef struct RelOptInfo * involving this rel */ bool has_eclass_joins; /* T means joininfo is incomplete */ ! /* used by "other" relations */ ! Relids top_parent_relids; /* Relids of topmost parents */ /* used for partitioned relations */ PartitionScheme part_scheme; /* Partitioning scheme. */ --- 687,698 ---- * involving this rel */ bool has_eclass_joins; /* T means joininfo is incomplete */ ! /* used by partitionwise joins: */ ! bool consider_partitionwise_join; /* consider partitionwise ! * join paths? (if ! * partitioned rel) */ ! Relids top_parent_relids; /* Relids of topmost parents (if "other" ! * rel) */ /* used for partitioned relations */ PartitionScheme part_scheme; /* Partitioning scheme. */ *** a/src/test/regress/expected/partition_aggregate.out --- b/src/test/regress/expected/partition_aggregate.out *************** *** 535,540 **** SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2 --- 535,572 ---- (6 rows) RESET enable_hashagg; + -- Check with whole-row reference; partitionwise aggregation does not apply + EXPLAIN (COSTS OFF) + SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; + QUERY PLAN + ------------------------------------------------------------- + Sort + Sort Key: t1.x, (sum(t1.y)), (count(((t1.*)::pagg_tab1))) + -> HashAggregate + Group Key: t1.x + -> Hash Join + Hash Cond: (t1.x = t2.y) + -> Append + -> Seq Scan on pagg_tab1_p1 t1 + -> Seq Scan on pagg_tab1_p2 t1_1 + -> Seq Scan on pagg_tab1_p3 t1_2 + -> Hash + -> Append + -> Seq Scan on pagg_tab2_p1 t2 + -> Seq Scan on pagg_tab2_p2 t2_1 + -> Seq Scan on pagg_tab2_p3 t2_2 + (15 rows) + + SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; + x | sum | count + ----+------+------- + 0 | 500 | 100 + 6 | 1100 | 100 + 12 | 700 | 100 + 18 | 1300 | 100 + 24 | 900 | 100 + (5 rows) + -- Check with LEFT/RIGHT/FULL OUTER JOINs which produces NULL values for -- aggregation -- LEFT JOIN, should produce partial partitionwise aggregation plan as *** a/src/test/regress/expected/partition_join.out --- b/src/test/regress/expected/partition_join.out *************** *** 69,94 **** SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER -------------------------------------------------- Sort Sort Key: t1.a, t2.b ! -> Append ! -> Hash Right Join ! Hash Cond: (t2.b = t1.a) -> Seq Scan on prt2_p1 t2 ! -> Hash -> Seq Scan on prt1_p1 t1 Filter: (b = 0) - -> Hash Right Join - Hash Cond: (t2_1.b = t1_1.a) - -> Seq Scan on prt2_p2 t2_1 - -> Hash -> Seq Scan on prt1_p2 t1_1 Filter: (b = 0) - -> Hash Right Join - Hash Cond: (t2_2.b = t1_2.a) - -> Seq Scan on prt2_p3 t2_2 - -> Hash -> Seq Scan on prt1_p3 t1_2 Filter: (b = 0) ! (21 rows) SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; t1 | t2 --- 69,89 ---- -------------------------------------------------- Sort Sort Key: t1.a, t2.b ! -> Hash Right Join ! Hash Cond: (t2.b = t1.a) ! -> Append -> Seq Scan on prt2_p1 t2 ! -> Seq Scan on prt2_p2 t2_1 ! -> Seq Scan on prt2_p3 t2_2 ! -> Hash ! -> Append -> Seq Scan on prt1_p1 t1 Filter: (b = 0) -> Seq Scan on prt1_p2 t1_1 Filter: (b = 0) -> Seq Scan on prt1_p3 t1_2 Filter: (b = 0) ! (16 rows) SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; t1 | t2 *************** *** 1042,1047 **** SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * --- 1037,1076 ---- 400 | (9 rows) + -- merge join when expression with whole-row reference needs to be sorted; + -- partitionwise join does not apply + EXPLAIN (COSTS OFF) + SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a; + QUERY PLAN + ----------------------------------------------------------------------------------------- + Merge Join + Merge Cond: ((t1.a = t2.b) AND (((((t1.*)::prt1))::text) = ((((t2.*)::prt2))::text))) + -> Sort + Sort Key: t1.a, ((((t1.*)::prt1))::text) + -> Result + -> Append + -> Seq Scan on prt1_p1 t1 + -> Seq Scan on prt1_p2 t1_1 + -> Seq Scan on prt1_p3 t1_2 + -> Sort + Sort Key: t2.b, ((((t2.*)::prt2))::text) + -> Result + -> Append + -> Seq Scan on prt2_p1 t2 + -> Seq Scan on prt2_p2 t2_1 + -> Seq Scan on prt2_p3 t2_2 + (16 rows) + + SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a; + a | b + ----+---- + 0 | 0 + 6 | 6 + 12 | 12 + 18 | 18 + 24 | 24 + (5 rows) + RESET enable_hashjoin; RESET enable_nestloop; -- *** a/src/test/regress/sql/partition_aggregate.sql --- b/src/test/regress/sql/partition_aggregate.sql *************** *** 123,128 **** SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2 --- 123,133 ---- SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.y HAVING avg(t1.x) > 10 ORDER BY 1, 2, 3; RESET enable_hashagg; + -- Check with whole-row reference; partitionwise aggregation does not apply + EXPLAIN (COSTS OFF) + SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; + SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; + -- Check with LEFT/RIGHT/FULL OUTER JOINs which produces NULL values for -- aggregation *** a/src/test/regress/sql/partition_join.sql --- b/src/test/regress/sql/partition_join.sql *************** *** 160,165 **** EXPLAIN (COSTS OFF) --- 160,171 ---- SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; + -- merge join when expression with whole-row reference needs to be sorted; + -- partitionwise join does not apply + EXPLAIN (COSTS OFF) + SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a; + SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a; + RESET enable_hashjoin; RESET enable_nestloop; --------------080508030800090607080009--