agora inbox for [email protected]
help / color / mirror / Atom feed[PATCH 02/10] Run 011_crash_recovery.pl with wal_level=minimal
8+ messages / 4 participants
[nested] [flat]
* [PATCH 02/10] Run 011_crash_recovery.pl with wal_level=minimal
@ 2021-03-08 06:32 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Kyotaro Horiguchi @ 2021-03-08 06:32 UTC (permalink / raw)
The test doesn't need that feature and pg_current_xact_id() is better
exercised by turning off the feature.
Copied from: https://www.postgresql.org/message-id/20210308.173242.463790587797836129.horikyota.ntt%40gmail.com
---
src/test/recovery/t/011_crash_recovery.pl | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/test/recovery/t/011_crash_recovery.pl b/src/test/recovery/t/011_crash_recovery.pl
index 10cd98f70a..690655dda2 100644
--- a/src/test/recovery/t/011_crash_recovery.pl
+++ b/src/test/recovery/t/011_crash_recovery.pl
@@ -11,7 +11,7 @@ use Config;
plan tests => 3;
my $node = get_new_node('primary');
-$node->init(allows_streaming => 1);
+$node->init();
$node->start;
my ($stdin, $stdout, $stderr) = ('', '', '');
--
2.17.0
--XsQoSWH+UP9D9v3l
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0003-Make-sure-published-XIDs-are-persistent.patch"
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using each rel as both outer and inner for JOIN_ANTI
@ 2022-08-02 07:13 Richard Guo <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Richard Guo @ 2022-08-02 07:13 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Ronan Dunklau <[email protected]>; Pg Hackers <[email protected]>; Emre Hasegeli <[email protected]>
On Sun, Jul 31, 2022 at 12:07 AM Tom Lane <[email protected]> wrote:
> Richard Guo <[email protected]> writes:
> > [ v4-0001-Using-each-rel-as-both-outer-and-inner-for-anti-j.patch ]
>
> I took a quick look through this. The executor changes are indeed
> impressively short, but that's largely because you've paid zero
> attention to updating obsoleted comments. For example, in
> nodeHashjoin.c there are lots of references to right/full joins
> that likely now need to cover right-anti. I'm not sure that the
> empty-rel startup optimizations are correct for this case, either.
Thanks for the review! Yeah, you're right. I neglected to update the
related comments. Will do that in the new patch. For the empty-rel
startup optimizations, since the right-anti join also does null-fill on
inner relation (the HJ_FILL_INNER case), I think we cannot skip building
the hash table even when the outer rel is completely empty.
> I don't have a warm feeling about the planner changes being correct:
> it looks like what you mostly did was to treat JOIN_RIGHT_ANTI
> identically to JOIN_ANTI everywhere, which is surely wrong.
> As an example of this, optimizer/README mentions
>
> Similarly, parameterized paths do not normally get preference in add_path
> for having cheap startup cost; that's seldom of much value when on the
> inside of a nestloop, so it seems not worth keeping extra paths solely
> for
> that. An exception occurs for parameterized paths for the RHS relation
> of
> a SEMI or ANTI join: in those cases, we can stop the inner scan after the
> first match, so it's primarily startup not total cost that we care about.
>
> For RIGHT_ANTI it'd become startup of the outer scan that counts, but
> I don't think you've gotten that right here.
I think JOIN_RIGHT_ANTI behaves more like JOIN_RIGHT, except that
JOIN_RIGHT returns a matched tuple while JOIN_RIGHT_ANTI does not. For
each outer tuple, right-anti needs to scan the inner rel for every match
and mark its hashtable entry. So I think the right-anti join should not
belong to the case 'in those cases, we can stop the inner scan after the
first match, so it's primarily startup not total cost that we care
about.' Am I thinking it correctly?
> [ wanders away wondering if JOIN_RIGHT_SEMI should become a thing ... ]
Maybe this is something we can do. Currently for the query below:
# explain select * from foo where a in (select c from bar);
QUERY PLAN
-------------------------------------------------------------------------
Hash Semi Join (cost=154156.00..173691.29 rows=10 width=8)
Hash Cond: (foo.a = bar.c)
-> Seq Scan on foo (cost=0.00..1.10 rows=10 width=8)
-> Hash (cost=72124.00..72124.00 rows=5000000 width=4)
-> Seq Scan on bar (cost=0.00..72124.00 rows=5000000 width=4)
(5 rows)
I believe we can get a cheaper plan if we are able to swap the outer and
inner for SEMI JOIN and use the smaller 'foo' as inner rel.
Thanks
Richard
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using each rel as both outer and inner for JOIN_ANTI
@ 2022-08-09 08:12 Richard Guo <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Richard Guo @ 2022-08-09 08:12 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Ronan Dunklau <[email protected]>; Pg Hackers <[email protected]>; Emre Hasegeli <[email protected]>
On Tue, Aug 2, 2022 at 3:13 PM Richard Guo <[email protected]> wrote:
> On Sun, Jul 31, 2022 at 12:07 AM Tom Lane <[email protected]> wrote:
>
>> I took a quick look through this. The executor changes are indeed
>> impressively short, but that's largely because you've paid zero
>> attention to updating obsoleted comments. For example, in
>> nodeHashjoin.c there are lots of references to right/full joins
>> that likely now need to cover right-anti. I'm not sure that the
>> empty-rel startup optimizations are correct for this case, either.
>
>
> Thanks for the review! Yeah, you're right. I neglected to update the
> related comments. Will do that in the new patch. For the empty-rel
> startup optimizations, since the right-anti join also does null-fill on
> inner relation (the HJ_FILL_INNER case), I think we cannot skip building
> the hash table even when the outer rel is completely empty.
>
Here is the new patch which addresses the obsoleted comments.
Thanks
Richard
Attachments:
[application/octet-stream] v5-0001-Using-each-rel-as-both-outer-and-inner-for-anti-j.patch (36.9K, ../../CAMbWs4_w=638iYjZH-_cK3xMOVBFuVQus_MWageYUN6u=TJUOA@mail.gmail.com/3-v5-0001-Using-each-rel-as-both-outer-and-inner-for-anti-j.patch)
download | inline diff:
From 12aabf4cc858e00e266d1b5224685b7496f352c2 Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Mon, 8 Aug 2022 14:44:01 +0800
Subject: [PATCH v5] Using each rel as both outer and inner for anti joins
---
src/backend/commands/explain.c | 3 +
src/backend/executor/nodeHashjoin.c | 41 +--
src/backend/executor/nodeMergejoin.c | 32 ++-
src/backend/optimizer/path/costsize.c | 3 +-
src/backend/optimizer/path/joinpath.c | 55 ++--
src/backend/optimizer/path/joinrels.c | 3 +
src/backend/optimizer/path/pathkeys.c | 10 +-
src/backend/optimizer/plan/setrefs.c | 1 +
src/include/nodes/execnodes.h | 3 +-
src/include/nodes/nodes.h | 4 +-
src/test/regress/expected/partition_join.out | 276 ++++++++++---------
11 files changed, 238 insertions(+), 193 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index e078456b19..cb093f31f1 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1550,6 +1550,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
case JOIN_ANTI:
jointype = "Anti";
break;
+ case JOIN_RIGHT_ANTI:
+ jointype = "Right Anti";
+ break;
default:
jointype = "???";
break;
diff --git a/src/backend/executor/nodeHashjoin.c b/src/backend/executor/nodeHashjoin.c
index 87403e2478..140db24afe 100644
--- a/src/backend/executor/nodeHashjoin.c
+++ b/src/backend/executor/nodeHashjoin.c
@@ -217,10 +217,10 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
/*
* If the outer relation is completely empty, and it's not
- * right/full join, we can quit without building the hash
- * table. However, for an inner join it is only a win to
- * check this when the outer relation's startup cost is less
- * than the projected cost of building the hash table.
+ * right/right-anti/full join, we can quit without building
+ * the hash table. However, for an inner join it is only a
+ * win to check this when the outer relation's startup cost is
+ * less than the projected cost of building the hash table.
* Otherwise it's best to build the hash table first and see
* if the inner relation is empty. (When it's a left join, we
* should always make this check, since we aren't going to be
@@ -458,11 +458,10 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
if (parallel)
{
/*
- * Full/right outer joins are currently not supported
- * for parallel joins, so we don't need to set the
- * match bit. Experiments show that it's worth
- * avoiding the shared memory traffic on large
- * systems.
+ * Full/right/right-anti outer joins are currently not
+ * supported for parallel joins, so we don't need to
+ * set the match bit. Experiments show that it's worth
+ * avoiding the shared memory traffic on large systems.
*/
Assert(!HJ_FILL_INNER(node));
}
@@ -482,6 +481,14 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
continue;
}
+ /*
+ * In a right-antijoin, we never return a matched tuple.
+ * And we need to use current outer tuple to scan the
+ * bucket for matches
+ */
+ if (node->js.jointype == JOIN_RIGHT_ANTI)
+ continue;
+
/*
* If we only need to join to the first matching inner
* tuple, then consider returning this one, but after that
@@ -527,9 +534,10 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
case HJ_FILL_INNER_TUPLES:
/*
- * We have finished a batch, but we are doing right/full join,
- * so any unmatched inner tuples in the hashtable have to be
- * emitted before we continue to the next batch.
+ * We have finished a batch, but we are doing
+ * right/right-anti/full join, so any unmatched inner tuples in
+ * the hashtable have to be emitted before we continue to the
+ * next batch.
*/
if (!ExecScanHashTableForUnmatched(node, econtext))
{
@@ -694,6 +702,7 @@ ExecInitHashJoin(HashJoin *node, EState *estate, int eflags)
ExecInitNullTupleSlot(estate, innerDesc, &TTSOpsVirtual);
break;
case JOIN_RIGHT:
+ case JOIN_RIGHT_ANTI:
hjstate->hj_NullOuterTupleSlot =
ExecInitNullTupleSlot(estate, outerDesc, &TTSOpsVirtual);
break;
@@ -987,8 +996,8 @@ ExecHashJoinNewBatch(HashJoinState *hjstate)
* side, but there are exceptions:
*
* 1. In a left/full outer join, we have to process outer batches even if
- * the inner batch is empty. Similarly, in a right/full outer join, we
- * have to process inner batches even if the outer batch is empty.
+ * the inner batch is empty. Similarly, in a right/right-anti/full outer
+ * join, we have to process inner batches even if the outer batch is empty.
*
* 2. If we have increased nbatch since the initial estimate, we have to
* scan inner batches since they might contain tuples that need to be
@@ -1308,8 +1317,8 @@ ExecReScanHashJoin(HashJoinState *node)
/*
* Okay to reuse the hash table; needn't rescan inner, either.
*
- * However, if it's a right/full join, we'd better reset the
- * inner-tuple match flags contained in the table.
+ * However, if it's a right/right-anti/full join, we'd better reset
+ * the inner-tuple match flags contained in the table.
*/
if (HJ_FILL_INNER(node))
ExecHashTableResetMatchFlags(node->hj_HashTable);
diff --git a/src/backend/executor/nodeMergejoin.c b/src/backend/executor/nodeMergejoin.c
index fed345eae5..b16d2a066b 100644
--- a/src/backend/executor/nodeMergejoin.c
+++ b/src/backend/executor/nodeMergejoin.c
@@ -805,6 +805,13 @@ ExecMergeJoin(PlanState *pstate)
break;
}
+ /*
+ * In a right-antijoin, we never return a matched tuple.
+ * And we need to use current outer tuple for further scans
+ */
+ if (node->js.jointype == JOIN_RIGHT_ANTI)
+ break;
+
/*
* If we only need to join to the first matching inner
* tuple, then consider returning this one, but after that
@@ -1063,12 +1070,12 @@ ExecMergeJoin(PlanState *pstate)
* them will match this new outer tuple and therefore
* won't be emitted as fill tuples. This works *only*
* because we require the extra joinquals to be constant
- * when doing a right or full join --- otherwise some of
- * the rescanned tuples might fail the extra joinquals.
- * This obviously won't happen for a constant-true extra
- * joinqual, while the constant-false case is handled by
- * forcing the merge clause to never match, so we never
- * get here.
+ * when doing a right, right-anti or full join ---
+ * otherwise some of the rescanned tuples might fail the
+ * extra joinquals. This obviously won't happen for a
+ * constant-true extra joinqual, while the constant-false
+ * case is handled by forcing the merge clause to never
+ * match, so we never get here.
*/
if (!node->mj_SkipMarkRestore)
{
@@ -1332,8 +1339,8 @@ ExecMergeJoin(PlanState *pstate)
/*
* EXEC_MJ_ENDOUTER means we have run out of outer tuples, but
- * are doing a right/full join and therefore must null-fill
- * any remaining unmatched inner tuples.
+ * are doing a right/right-anti/full join and therefore must
+ * null-fill any remaining unmatched inner tuples.
*/
case EXEC_MJ_ENDOUTER:
MJ_printf("ExecMergeJoin: EXEC_MJ_ENDOUTER\n");
@@ -1554,14 +1561,15 @@ ExecInitMergeJoin(MergeJoin *node, EState *estate, int eflags)
ExecInitNullTupleSlot(estate, innerDesc, &TTSOpsVirtual);
break;
case JOIN_RIGHT:
+ case JOIN_RIGHT_ANTI:
mergestate->mj_FillOuter = false;
mergestate->mj_FillInner = true;
mergestate->mj_NullOuterTupleSlot =
ExecInitNullTupleSlot(estate, outerDesc, &TTSOpsVirtual);
/*
- * Can't handle right or full join with non-constant extra
- * joinclauses. This should have been caught by planner.
+ * Can't handle right, right-anti or full join with non-constant
+ * extra joinclauses. This should have been caught by planner.
*/
if (!check_constant_qual(node->join.joinqual,
&mergestate->mj_ConstFalseJoin))
@@ -1578,8 +1586,8 @@ ExecInitMergeJoin(MergeJoin *node, EState *estate, int eflags)
ExecInitNullTupleSlot(estate, innerDesc, &TTSOpsVirtual);
/*
- * Can't handle right or full join with non-constant extra
- * joinclauses. This should have been caught by planner.
+ * Can't handle right, right-anti or full join with non-constant
+ * extra joinclauses. This should have been caught by planner.
*/
if (!check_constant_qual(node->join.joinqual,
&mergestate->mj_ConstFalseJoin))
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index fb28e6411a..2a82692801 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -3645,7 +3645,8 @@ initial_cost_mergejoin(PlannerInfo *root, JoinCostWorkspace *workspace,
outerstartsel = 0.0;
outerendsel = 1.0;
}
- else if (jointype == JOIN_RIGHT)
+ else if (jointype == JOIN_RIGHT ||
+ jointype == JOIN_RIGHT_ANTI)
{
innerstartsel = 0.0;
innerendsel = 1.0;
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 2a3f0ab7bf..6a3b5adcba 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -284,8 +284,9 @@ add_paths_to_joinrel(PlannerInfo *root,
* 2. Consider paths where the outer relation need not be explicitly
* sorted. This includes both nestloops and mergejoins where the outer
* path is already ordered. Again, skip this if we can't mergejoin.
- * (That's okay because we know that nestloop can't handle right/full
- * joins at all, so it wouldn't work in the prohibited cases either.)
+ * (That's okay because we know that nestloop can't handle
+ * right/right-anti/full joins at all, so it wouldn't work in the
+ * prohibited cases either.)
*/
if (mergejoin_allowed)
match_unsorted_outer(root, joinrel, outerrel, innerrel,
@@ -1204,14 +1205,15 @@ sort_inner_and_outer(PlannerInfo *root,
* If the joinrel is parallel-safe, we may be able to consider a partial
* merge join. However, we can't handle JOIN_UNIQUE_OUTER, because the
* outer path will be partial, and therefore we won't be able to properly
- * guarantee uniqueness. Similarly, we can't handle JOIN_FULL and
- * JOIN_RIGHT, because they can produce false null extended rows. Also,
- * the resulting path must not be parameterized.
+ * guarantee uniqueness. Similarly, we can't handle JOIN_FULL, JOIN_RIGHT
+ * and JOIN_RIGHT_ANTI, because they can produce false null extended rows.
+ * Also, the resulting path must not be parameterized.
*/
if (joinrel->consider_parallel &&
save_jointype != JOIN_UNIQUE_OUTER &&
save_jointype != JOIN_FULL &&
save_jointype != JOIN_RIGHT &&
+ save_jointype != JOIN_RIGHT_ANTI &&
outerrel->partial_pathlist != NIL &&
bms_is_empty(joinrel->lateral_relids))
{
@@ -1606,10 +1608,10 @@ match_unsorted_outer(PlannerInfo *root,
/*
* Nestloop only supports inner, left, semi, and anti joins. Also, if we
- * are doing a right or full mergejoin, we must use *all* the mergeclauses
- * as join clauses, else we will not have a valid plan. (Although these
- * two flags are currently inverses, keep them separate for clarity and
- * possible future changes.)
+ * are doing a right, right-anti or full mergejoin, we must use *all* the
+ * mergeclauses as join clauses, else we will not have a valid plan.
+ * (Although these two flags are currently inverses, keep them separate
+ * for clarity and possible future changes.)
*/
switch (jointype)
{
@@ -1621,6 +1623,7 @@ match_unsorted_outer(PlannerInfo *root,
useallclauses = false;
break;
case JOIN_RIGHT:
+ case JOIN_RIGHT_ANTI:
case JOIN_FULL:
nestjoinOK = false;
useallclauses = true;
@@ -1792,13 +1795,14 @@ match_unsorted_outer(PlannerInfo *root,
* handle JOIN_UNIQUE_OUTER, because the outer path will be partial, and
* therefore we won't be able to properly guarantee uniqueness. Nor can
* we handle joins needing lateral rels, since partial paths must not be
- * parameterized. Similarly, we can't handle JOIN_FULL and JOIN_RIGHT,
- * because they can produce false null extended rows.
+ * parameterized. Similarly, we can't handle JOIN_FULL, JOIN_RIGHT and
+ * JOIN_RIGHT_ANTI, because they can produce false null extended rows.
*/
if (joinrel->consider_parallel &&
save_jointype != JOIN_UNIQUE_OUTER &&
save_jointype != JOIN_FULL &&
save_jointype != JOIN_RIGHT &&
+ save_jointype != JOIN_RIGHT_ANTI &&
outerrel->partial_pathlist != NIL &&
bms_is_empty(joinrel->lateral_relids))
{
@@ -2134,17 +2138,19 @@ hash_inner_and_outer(PlannerInfo *root,
* partial hash join. However, we can't handle JOIN_UNIQUE_OUTER,
* because the outer path will be partial, and therefore we won't be
* able to properly guarantee uniqueness. Similarly, we can't handle
- * JOIN_FULL and JOIN_RIGHT, because they can produce false null
- * extended rows. Also, the resulting path must not be parameterized.
- * We would be able to support JOIN_FULL and JOIN_RIGHT for Parallel
- * Hash, since in that case we're back to a single hash table with a
- * single set of match bits for each batch, but that will require
- * figuring out a deadlock-free way to wait for the probe to finish.
+ * JOIN_FULL, JOIN_RIGHT and JOIN_RIGHT_ANTI, because they can produce
+ * false null extended rows. Also, the resulting path must not be
+ * parameterized. We would be able to support JOIN_FULL, JOIN_RIGHT
+ * and JOIN_RIGHT_ANTI for Parallel Hash, since in that case we're
+ * back to a single hash table with a single set of match bits for
+ * each batch, but that will require figuring out a deadlock-free way
+ * to wait for the probe to finish.
*/
if (joinrel->consider_parallel &&
save_jointype != JOIN_UNIQUE_OUTER &&
save_jointype != JOIN_FULL &&
save_jointype != JOIN_RIGHT &&
+ save_jointype != JOIN_RIGHT_ANTI &&
outerrel->partial_pathlist != NIL &&
bms_is_empty(joinrel->lateral_relids))
{
@@ -2201,11 +2207,11 @@ hash_inner_and_outer(PlannerInfo *root,
* Select mergejoin clauses that are usable for a particular join.
* Returns a list of RestrictInfo nodes for those clauses.
*
- * *mergejoin_allowed is normally set to true, but it is set to false if
- * this is a right/full join and there are nonmergejoinable join clauses.
- * The executor's mergejoin machinery cannot handle such cases, so we have
- * to avoid generating a mergejoin plan. (Note that this flag does NOT
- * consider whether there are actually any mergejoinable clauses. This is
+ * *mergejoin_allowed is normally set to true, but it is set to false if this
+ * is a right/right-anti/full join and there are nonmergejoinable join
+ * clauses. The executor's mergejoin machinery cannot handle such cases, so
+ * we have to avoid generating a mergejoin plan. (Note that this flag does
+ * NOT consider whether there are actually any mergejoinable clauses. This is
* correct because in some cases we need to build a clauseless mergejoin.
* Simply returning NIL is therefore not enough to distinguish safe from
* unsafe cases.)
@@ -2251,8 +2257,8 @@ select_mergejoin_clauses(PlannerInfo *root,
{
/*
* The executor can handle extra joinquals that are constants, but
- * not anything else, when doing right/full merge join. (The
- * reason to support constants is so we can do FULL JOIN ON
+ * not anything else, when doing right/right-anti/full merge join.
+ * (The reason to support constants is so we can do FULL JOIN ON
* FALSE.)
*/
if (!restrictinfo->clause || !IsA(restrictinfo->clause, Const))
@@ -2307,6 +2313,7 @@ select_mergejoin_clauses(PlannerInfo *root,
switch (jointype)
{
case JOIN_RIGHT:
+ case JOIN_RIGHT_ANTI:
case JOIN_FULL:
*mergejoin_allowed = !have_nonmergeable_joinclause;
break;
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index 9da3ff2f9a..c6e5e0195c 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -915,6 +915,9 @@ populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1,
add_paths_to_joinrel(root, joinrel, rel1, rel2,
JOIN_ANTI, sjinfo,
restrictlist);
+ add_paths_to_joinrel(root, joinrel, rel2, rel1,
+ JOIN_RIGHT_ANTI, sjinfo,
+ restrictlist);
break;
default:
/* other values not expected here */
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 1fa7fc99b5..4504cd1fd5 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -1626,9 +1626,9 @@ find_var_for_subquery_tle(RelOptInfo *rel, TargetEntry *tle)
* Build the path keys for a join relation constructed by mergejoin or
* nestloop join. This is normally the same as the outer path's keys.
*
- * EXCEPTION: in a FULL or RIGHT join, we cannot treat the result as
- * having the outer path's path keys, because null lefthand rows may be
- * inserted at random points. It must be treated as unsorted.
+ * EXCEPTION: in a FULL, RIGHT or RIGHT_ANTI join, we cannot treat the
+ * result as having the outer path's path keys, because null lefthand rows
+ * may be inserted at random points. It must be treated as unsorted.
*
* We truncate away any pathkeys that are uninteresting for higher joins.
*
@@ -1644,7 +1644,9 @@ build_join_pathkeys(PlannerInfo *root,
JoinType jointype,
List *outer_pathkeys)
{
- if (jointype == JOIN_FULL || jointype == JOIN_RIGHT)
+ if (jointype == JOIN_FULL ||
+ jointype == JOIN_RIGHT ||
+ jointype == JOIN_RIGHT_ANTI)
return NIL;
/*
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 1cb0abdbc1..51f548041e 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -2252,6 +2252,7 @@ set_join_references(PlannerInfo *root, Join *join, int rtoffset)
inner_itlist->has_non_vars = false;
break;
case JOIN_RIGHT:
+ case JOIN_RIGHT_ANTI:
outer_itlist->has_non_vars = false;
break;
case JOIN_FULL:
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 01b1727fc0..3b3f5b5fd8 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2051,7 +2051,8 @@ typedef struct MergeJoinState
* OuterTupleSlot is empty!)
* hj_OuterTupleSlot tuple slot for outer tuples
* hj_HashTupleSlot tuple slot for inner (hashed) tuples
- * hj_NullOuterTupleSlot prepared null tuple for right/full outer joins
+ * hj_NullOuterTupleSlot prepared null tuple for right/right-anti/full
+ * outer joins
* hj_NullInnerTupleSlot prepared null tuple for left/full outer joins
* hj_FirstOuterTupleSlot first tuple retrieved from outer plan
* hj_JoinState current state of ExecHashJoin state machine
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index cdd6debfa0..4a9aa2308f 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -300,6 +300,7 @@ typedef enum JoinType
*/
JOIN_SEMI, /* 1 copy of each LHS row that has match(es) */
JOIN_ANTI, /* 1 copy of each LHS row that has no match */
+ JOIN_RIGHT_ANTI, /* 1 copy of each RHS row that has no match */
/*
* These codes are used internally in the planner, but are not supported
@@ -332,7 +333,8 @@ typedef enum JoinType
((1 << JOIN_LEFT) | \
(1 << JOIN_FULL) | \
(1 << JOIN_RIGHT) | \
- (1 << JOIN_ANTI))) != 0)
+ (1 << JOIN_ANTI) | \
+ (1 << JOIN_RIGHT_ANTI))) != 0)
/*
* AggStrategy -
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 03926a8413..852b740416 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -2403,24 +2403,24 @@ SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t
Sort
Sort Key: t1.a
-> Append
- -> Hash Anti Join
- Hash Cond: (t1_1.a = t2_1.b)
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
+ -> Hash Right Anti Join
+ Hash Cond: (t2_1.b = t1_1.a)
+ -> Seq Scan on prt2_adv_p1 t2_1
-> Hash
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Hash Anti Join
- Hash Cond: (t1_2.a = t2_2.b)
- -> Seq Scan on prt1_adv_p2 t1_2
- Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p1 t1_1
+ Filter: (b = 0)
+ -> Hash Right Anti Join
+ Hash Cond: (t2_2.b = t1_2.a)
+ -> Seq Scan on prt2_adv_p2 t2_2
-> Hash
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Hash Anti Join
- Hash Cond: (t1_3.a = t2_3.b)
- -> Seq Scan on prt1_adv_p3 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p2 t1_2
+ Filter: (b = 0)
+ -> Hash Right Anti Join
+ Hash Cond: (t2_3.b = t1_3.a)
+ -> Seq Scan on prt2_adv_p3 t2_3
-> Hash
- -> Seq Scan on prt2_adv_p3 t2_3
+ -> Seq Scan on prt1_adv_p3 t1_3
+ Filter: (b = 0)
(21 rows)
SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
@@ -2638,24 +2638,24 @@ SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t
Sort
Sort Key: t1.a
-> Append
- -> Hash Anti Join
- Hash Cond: (t1_1.a = t2_1.b)
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
+ -> Hash Right Anti Join
+ Hash Cond: (t2_1.b = t1_1.a)
+ -> Seq Scan on prt2_adv_p1 t2_1
-> Hash
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Hash Anti Join
- Hash Cond: (t1_2.a = t2_2.b)
- -> Seq Scan on prt1_adv_p2 t1_2
- Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p1 t1_1
+ Filter: (b = 0)
+ -> Hash Right Anti Join
+ Hash Cond: (t2_2.b = t1_2.a)
+ -> Seq Scan on prt2_adv_p2 t2_2
-> Hash
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Hash Anti Join
- Hash Cond: (t1_3.a = t2_3.b)
- -> Seq Scan on prt1_adv_p3 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p2 t1_2
+ Filter: (b = 0)
+ -> Hash Right Anti Join
+ Hash Cond: (t2_3.b = t1_3.a)
+ -> Seq Scan on prt2_adv_p3 t2_3
-> Hash
- -> Seq Scan on prt2_adv_p3 t2_3
+ -> Seq Scan on prt1_adv_p3 t1_3
+ Filter: (b = 0)
(21 rows)
SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
@@ -2671,26 +2671,26 @@ SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t
-- partitions on the nullable side
EXPLAIN (COSTS OFF)
SELECT t1.* FROM prt2_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt1_adv t2 WHERE t1.b = t2.a) AND t1.a = 0 ORDER BY t1.b;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------
Sort
Sort Key: t1.b
- -> Hash Anti Join
- Hash Cond: (t1.b = t2.a)
+ -> Hash Right Anti Join
+ Hash Cond: (t2.a = t1.b)
-> Append
- -> Seq Scan on prt2_adv_p1 t1_1
- Filter: (a = 0)
- -> Seq Scan on prt2_adv_p2 t1_2
- Filter: (a = 0)
- -> Seq Scan on prt2_adv_p3 t1_3
- Filter: (a = 0)
- -> Seq Scan on prt2_adv_extra t1_4
- Filter: (a = 0)
+ -> Seq Scan on prt1_adv_p1 t2_1
+ -> Seq Scan on prt1_adv_p2 t2_2
+ -> Seq Scan on prt1_adv_p3 t2_3
-> Hash
-> Append
- -> Seq Scan on prt1_adv_p1 t2_1
- -> Seq Scan on prt1_adv_p2 t2_2
- -> Seq Scan on prt1_adv_p3 t2_3
+ -> Seq Scan on prt2_adv_p1 t1_1
+ Filter: (a = 0)
+ -> Seq Scan on prt2_adv_p2 t1_2
+ Filter: (a = 0)
+ -> Seq Scan on prt2_adv_p3 t1_3
+ Filter: (a = 0)
+ -> Seq Scan on prt2_adv_extra t1_4
+ Filter: (a = 0)
(18 rows)
-- full join; currently we can't do partitioned join if there are no matched
@@ -2858,25 +2858,25 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 LEFT JOIN prt2_adv t2 ON (t1.a =
-- anti join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Sort
Sort Key: t1.a
- -> Hash Anti Join
- Hash Cond: (t1.a = t2.b)
+ -> Hash Right Anti Join
+ Hash Cond: (t2.b = t1.a)
-> Append
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p2 t1_2
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p3 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt2_adv_p1 t2_1
+ -> Seq Scan on prt2_adv_p2 t2_2
+ -> Seq Scan on prt2_adv_p3_1 t2_3
+ -> Seq Scan on prt2_adv_p3_2 t2_4
-> Hash
-> Append
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Seq Scan on prt2_adv_p3_1 t2_3
- -> Seq Scan on prt2_adv_p3_2 t2_4
+ -> Seq Scan on prt1_adv_p1 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p2 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p3 t1_3
+ Filter: (b = 0)
(17 rows)
-- full join
@@ -3280,27 +3280,30 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a =
-- anti join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
- -> Nested Loop Anti Join
- Join Filter: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
- -> Seq Scan on plt1_adv_p1 t1_1
- Filter: (b < 10)
+ -> Hash Right Anti Join
+ Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
-> Seq Scan on plt2_adv_p1 t2_1
- -> Nested Loop Anti Join
- Join Filter: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
- -> Seq Scan on plt1_adv_p2 t1_2
- Filter: (b < 10)
+ -> Hash
+ -> Seq Scan on plt1_adv_p1 t1_1
+ Filter: (b < 10)
+ -> Hash Right Anti Join
+ Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
-> Seq Scan on plt2_adv_p2 t2_2
- -> Nested Loop Anti Join
- Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
- -> Seq Scan on plt1_adv_p3 t1_3
- Filter: (b < 10)
+ -> Hash
+ -> Seq Scan on plt1_adv_p2 t1_2
+ Filter: (b < 10)
+ -> Hash Right Anti Join
+ Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
-> Seq Scan on plt2_adv_p3 t2_3
-(18 rows)
+ -> Hash
+ -> Seq Scan on plt1_adv_p3 t1_3
+ Filter: (b < 10)
+(21 rows)
SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
a | b | c
@@ -3493,27 +3496,30 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt2_adv t1 LEFT JOIN plt1_adv t2 ON (t1.a =
-- anti join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
- -> Nested Loop Anti Join
- Join Filter: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
- -> Seq Scan on plt1_adv_p1 t1_1
- Filter: (b < 10)
+ -> Hash Right Anti Join
+ Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
-> Seq Scan on plt2_adv_p1 t2_1
- -> Nested Loop Anti Join
- Join Filter: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
- -> Seq Scan on plt1_adv_p2 t1_2
- Filter: (b < 10)
+ -> Hash
+ -> Seq Scan on plt1_adv_p1 t1_1
+ Filter: (b < 10)
+ -> Hash Right Anti Join
+ Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
-> Seq Scan on plt2_adv_p2 t2_2
- -> Nested Loop Anti Join
- Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
- -> Seq Scan on plt1_adv_p3 t1_3
- Filter: (b < 10)
+ -> Hash
+ -> Seq Scan on plt1_adv_p2 t1_2
+ Filter: (b < 10)
+ -> Hash Right Anti Join
+ Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
-> Seq Scan on plt2_adv_p3 t2_3
-(18 rows)
+ -> Hash
+ -> Seq Scan on plt1_adv_p3 t1_3
+ Filter: (b < 10)
+(21 rows)
SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
a | b | c
@@ -3526,26 +3532,26 @@ SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t
-- partitions on the nullable side
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt2_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt1_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------
Sort
Sort Key: t1.a
- -> Hash Anti Join
- Hash Cond: ((t1.a = t2.a) AND (t1.c = t2.c))
+ -> Hash Right Anti Join
+ Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
-> Append
- -> Seq Scan on plt2_adv_extra t1_1
- Filter: (b < 10)
- -> Seq Scan on plt2_adv_p1 t1_2
- Filter: (b < 10)
- -> Seq Scan on plt2_adv_p2 t1_3
- Filter: (b < 10)
- -> Seq Scan on plt2_adv_p3 t1_4
- Filter: (b < 10)
+ -> Seq Scan on plt1_adv_p1 t2_1
+ -> Seq Scan on plt1_adv_p2 t2_2
+ -> Seq Scan on plt1_adv_p3 t2_3
-> Hash
-> Append
- -> Seq Scan on plt1_adv_p1 t2_1
- -> Seq Scan on plt1_adv_p2 t2_2
- -> Seq Scan on plt1_adv_p3 t2_3
+ -> Seq Scan on plt2_adv_extra t1_1
+ Filter: (b < 10)
+ -> Seq Scan on plt2_adv_p1 t1_2
+ Filter: (b < 10)
+ -> Seq Scan on plt2_adv_p2 t1_3
+ Filter: (b < 10)
+ -> Seq Scan on plt2_adv_p3 t1_4
+ Filter: (b < 10)
(18 rows)
-- full join; currently we can't do partitioned join if there are no matched
@@ -3655,25 +3661,25 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a =
-- anti join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Sort
Sort Key: t1.a
- -> Hash Anti Join
- Hash Cond: ((t1.a = t2.a) AND (t1.c = t2.c))
+ -> Hash Right Anti Join
+ Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
-> Append
- -> Seq Scan on plt1_adv_p1 t1_1
- Filter: (b < 10)
- -> Seq Scan on plt1_adv_p2 t1_2
- Filter: (b < 10)
- -> Seq Scan on plt1_adv_p3 t1_3
- Filter: (b < 10)
+ -> Seq Scan on plt2_adv_p1 t2_1
+ -> Seq Scan on plt2_adv_p2_1 t2_2
+ -> Seq Scan on plt2_adv_p2_2 t2_3
+ -> Seq Scan on plt2_adv_p3 t2_4
-> Hash
-> Append
- -> Seq Scan on plt2_adv_p1 t2_1
- -> Seq Scan on plt2_adv_p2_1 t2_2
- -> Seq Scan on plt2_adv_p2_2 t2_3
- -> Seq Scan on plt2_adv_p3 t2_4
+ -> Seq Scan on plt1_adv_p1 t1_1
+ Filter: (b < 10)
+ -> Seq Scan on plt1_adv_p2 t1_2
+ Filter: (b < 10)
+ -> Seq Scan on plt1_adv_p3 t1_3
+ Filter: (b < 10)
(17 rows)
-- full join
@@ -3830,28 +3836,30 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a =
-- anti join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
- -> Hash Anti Join
- Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
- -> Seq Scan on plt1_adv_p1_null t1_1
- Filter: (b < 10)
+ -> Hash Right Anti Join
+ Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
+ -> Seq Scan on plt2_adv_p1 t2_1
-> Hash
- -> Seq Scan on plt2_adv_p1 t2_1
- -> Nested Loop Anti Join
- Join Filter: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
- -> Seq Scan on plt1_adv_p2 t1_2
- Filter: (b < 10)
+ -> Seq Scan on plt1_adv_p1_null t1_1
+ Filter: (b < 10)
+ -> Hash Right Anti Join
+ Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
-> Seq Scan on plt2_adv_p2 t2_2
- -> Nested Loop Anti Join
- Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
- -> Seq Scan on plt1_adv_p3 t1_3
- Filter: (b < 10)
+ -> Hash
+ -> Seq Scan on plt1_adv_p2 t1_2
+ Filter: (b < 10)
+ -> Hash Right Anti Join
+ Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
-> Seq Scan on plt2_adv_p3_null t2_3
-(19 rows)
+ -> Hash
+ -> Seq Scan on plt1_adv_p3 t1_3
+ Filter: (b < 10)
+(21 rows)
SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
a | b | c
--
2.31.0
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using each rel as both outer and inner for JOIN_ANTI
@ 2022-08-09 10:54 Alvaro Herrera <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2022-08-09 10:54 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Tom Lane <[email protected]>; Ronan Dunklau <[email protected]>; Pg Hackers <[email protected]>; Emre Hasegeli <[email protected]>
Just for kicks, I ran query in your original post under EXPLAIN ANALYZE
in both patched and unpatched with this last version. I got this (best
of three):
Unpatched:
55432 16devel 437532=# explain (analyze, buffers) select * from foo left join bar on foo.a = bar.c where bar.c is null;
QUERY PLAN
────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Hash Anti Join (cost=159039.00..183457.23 rows=10 width=20) (actual time=482.788..483.182 rows=10 loops=1)
Hash Cond: (foo.a = bar.c)
Buffers: shared hit=161 read=21964, temp read=8 written=8
-> Seq Scan on foo (cost=0.00..1.10 rows=10 width=8) (actual time=0.020..0.022 rows=10 loops=1)
Buffers: shared hit=1
-> Hash (cost=72124.00..72124.00 rows=5000000 width=12) (actual time=482.128..482.129 rows=0 loops=1)
Buckets: 262144 Batches: 64 Memory Usage: 2048kB
Buffers: shared hit=160 read=21964
-> Seq Scan on bar (cost=0.00..72124.00 rows=5000000 width=12) (actual time=0.092..237.431 rows=5000000 loops=1)
Buffers: shared hit=160 read=21964
Planning Time: 0.182 ms
Execution Time: 483.248 ms
Patched:
QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Hash Right Anti Join (cost=1.23..90875.24 rows=10 width=20) (actual time=457.654..457.658 rows=10 loops=1)
Hash Cond: (bar.c = foo.a)
Buffers: shared hit=33 read=22092
-> Seq Scan on bar (cost=0.00..72124.00 rows=5000000 width=12) (actual time=0.020..229.097 rows=5000000 loops=1)
Buffers: shared hit=32 read=22092
-> Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.011..0.012 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on foo (cost=0.00..1.10 rows=10 width=8) (actual time=0.006..0.007 rows=10 loops=1)
Buffers: shared hit=1
Planning Time: 0.067 ms
Execution Time: 457.679 ms
I suppose this looks good as far as the plan goes, but the cost estimation
might be a little bit too optimistic: it is reporting that the new plan
costs 50% of the original, yet the execution time is only 5% lower.
I wonder where does time go (in unpatched) when seqscanning finishes
and before hashing starts.
(I had to disable JIT for the first one, as it insisted on JITting tuple
deforming.)
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Bob [Floyd] used to say that he was planning to get a Ph.D. by the "green
stamp method," namely by saving envelopes addressed to him as 'Dr. Floyd'.
After collecting 500 such letters, he mused, a university somewhere in
Arizona would probably grant him a degree. (Don Knuth)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using each rel as both outer and inner for JOIN_ANTI
@ 2022-08-10 07:57 Richard Guo <[email protected]>
parent: Alvaro Herrera <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Richard Guo @ 2022-08-10 07:57 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Tom Lane <[email protected]>; Ronan Dunklau <[email protected]>; Pg Hackers <[email protected]>; Emre Hasegeli <[email protected]>
On Tue, Aug 9, 2022 at 6:54 PM Alvaro Herrera <[email protected]>
wrote:
> I suppose this looks good as far as the plan goes, but the cost estimation
> might be a little bit too optimistic: it is reporting that the new plan
> costs 50% of the original, yet the execution time is only 5% lower.
Thanks for trying this patch. Yeah, the estimated cost doesn't match the
execution time here. I tried the query locally and here is what I got
(best of three):
Unpatched:
# explain analyze select * from foo left join bar on foo.a = bar.c where
bar.c is null;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=154156.00..173691.19 rows=1 width=16) (actual
time=1548.622..1548.624 rows=0 loops=1)
Hash Cond: (foo.a = bar.c)
-> Seq Scan on foo (cost=0.00..1.10 rows=10 width=8) (actual
time=0.024..0.026 rows=10 loops=1)
-> Hash (cost=72124.00..72124.00 rows=5000000 width=8) (actual
time=1443.157..1443.158 rows=5000000 loops=1)
Buckets: 262144 Batches: 64 Memory Usage: 5107kB
-> Seq Scan on bar (cost=0.00..72124.00 rows=5000000 width=8)
(actual time=0.045..481.059 rows=5000000 loops=1)
Planning Time: 0.262 ms
Execution Time: 1549.138 ms
(8 rows)
Patched:
# explain analyze select * from foo left join bar on foo.a = bar.c where
bar.c is null;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Hash Right Anti Join (cost=1.23..90875.33 rows=1 width=16) (actual
time=985.773..985.775 rows=0 loops=1)
Hash Cond: (bar.c = foo.a)
-> Seq Scan on bar (cost=0.00..72124.00 rows=5000000 width=8) (actual
time=0.095..438.333 rows=5000000 loops=1)
-> Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.076..0.077
rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on foo (cost=0.00..1.10 rows=10 width=8) (actual
time=0.060..0.064 rows=10 loops=1)
Planning Time: 0.290 ms
Execution Time: 985.830 ms
(8 rows)
Seems the cost matches the execution time better in my local box.
The right-anti join plan has the same cost estimation with right join
plan in this case. So would you please help to test what the right join
plan looks like in your env for the query below?
select * from foo left join bar on foo.a = bar.c;
Thanks
Richard
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using each rel as both outer and inner for JOIN_ANTI
@ 2022-08-10 08:40 Alvaro Herrera <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2022-08-10 08:40 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Tom Lane <[email protected]>; Ronan Dunklau <[email protected]>; Pg Hackers <[email protected]>; Emre Hasegeli <[email protected]>
On 2022-Aug-10, Richard Guo wrote:
> The right-anti join plan has the same cost estimation with right join
> plan in this case. So would you please help to test what the right join
> plan looks like in your env for the query below?
>
> select * from foo left join bar on foo.a = bar.c;
You're right, it does.
55432 16devel 475322=# explain (analyze, buffers) select * from foo left join bar on foo.a = bar.c;
QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Hash Right Join (cost=1.23..90875.24 rows=10 width=20) (actual time=456.410..456.415 rows=10 loops=1)
Hash Cond: (bar.c = foo.a)
Buffers: shared hit=15852 read=6273
-> Seq Scan on bar (cost=0.00..72124.00 rows=5000000 width=12) (actual time=0.036..210.468 rows=5000000 loops=1)
Buffers: shared hit=15852 read=6272
-> Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.037..0.038 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared read=1
-> Seq Scan on foo (cost=0.00..1.10 rows=10 width=8) (actual time=0.022..0.026 rows=10 loops=1)
Buffers: shared read=1
Planning:
Buffers: shared hit=92 read=13
Planning Time: 1.077 ms
Execution Time: 456.458 ms
(14 filas)
55432 16devel 475322=# explain (analyze, buffers) select * from foo left join bar on foo.a = bar.c where bar.c is null;
QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Hash Right Anti Join (cost=1.23..90875.24 rows=10 width=20) (actual time=451.747..451.751 rows=10 loops=1)
Hash Cond: (bar.c = foo.a)
Buffers: shared hit=15646 read=6479
-> Seq Scan on bar (cost=0.00..72124.00 rows=5000000 width=12) (actual time=0.048..204.940 rows=5000000 loops=1)
Buffers: shared hit=15645 read=6479
-> Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.030..0.031 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on foo (cost=0.00..1.10 rows=10 width=8) (actual time=0.017..0.020 rows=10 loops=1)
Buffers: shared hit=1
Planning Time: 0.227 ms
Execution Time: 451.793 ms
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Every machine is a smoke machine if you operate it wrong enough."
https://twitter.com/libseybieda/status/1541673325781196801
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Using each rel as both outer and inner for JOIN_ANTI
@ 2022-08-11 02:58 Richard Guo <[email protected]>
parent: Alvaro Herrera <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Richard Guo @ 2022-08-11 02:58 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Tom Lane <[email protected]>; Ronan Dunklau <[email protected]>; Pg Hackers <[email protected]>; Emre Hasegeli <[email protected]>
On Wed, Aug 10, 2022 at 4:40 PM Alvaro Herrera <[email protected]>
wrote:
> On 2022-Aug-10, Richard Guo wrote:
>
> > The right-anti join plan has the same cost estimation with right join
> > plan in this case. So would you please help to test what the right join
> > plan looks like in your env for the query below?
> >
> > select * from foo left join bar on foo.a = bar.c;
>
> You're right, it does.
>
> 55432 16devel 475322=# explain (analyze, buffers) select * from foo left
> join bar on foo.a = bar.c;
> QUERY PLAN
>
>
> ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
> Hash Right Join (cost=1.23..90875.24 rows=10 width=20) (actual
> time=456.410..456.415 rows=10 loops=1)
> Hash Cond: (bar.c = foo.a)
> Buffers: shared hit=15852 read=6273
> -> Seq Scan on bar (cost=0.00..72124.00 rows=5000000 width=12)
> (actual time=0.036..210.468 rows=5000000 loops=1)
> Buffers: shared hit=15852 read=6272
> -> Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.037..0.038
> rows=10 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 9kB
> Buffers: shared read=1
> -> Seq Scan on foo (cost=0.00..1.10 rows=10 width=8) (actual
> time=0.022..0.026 rows=10 loops=1)
> Buffers: shared read=1
> Planning:
> Buffers: shared hit=92 read=13
> Planning Time: 1.077 ms
> Execution Time: 456.458 ms
> (14 filas)
Thanks for help testing. Comparing the anti join plan and the right join
plan, the estimated cost and the execution time mismatch a lot. Seems
the cost estimate of hashjoin path is not that precise for this case
even in the unpatched codes. Maybe this is something we need to improve.
Thanks
Richard
^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH 7/9] no need for palloc0 here -- simple palloc is enough
@ 2026-03-12 15:13 Álvaro Herrera <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Álvaro Herrera @ 2026-03-12 15:13 UTC (permalink / raw)
---
src/backend/replication/pgoutput_repack/pgoutput_repack.c | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/backend/replication/pgoutput_repack/pgoutput_repack.c b/src/backend/replication/pgoutput_repack/pgoutput_repack.c
index 61cd7c52ae6..73aa6f0589c 100644
--- a/src/backend/replication/pgoutput_repack/pgoutput_repack.c
+++ b/src/backend/replication/pgoutput_repack/pgoutput_repack.c
@@ -197,8 +197,8 @@ store_change(LogicalDecodingContext *ctx, Relation relation,
bool *isnull;
desc = RelationGetDescr(relation);
- attrs = palloc0_array(Datum, desc->natts);
- isnull = palloc0_array(bool, desc->natts);
+ attrs = palloc_array(Datum, desc->natts);
+ isnull = palloc_array(bool, desc->natts);
heap_deform_tuple(tuple, desc, attrs, isnull);
--
2.47.3
--pnppmxqkefjd4hu2
Content-Type: text/plain; charset=utf-8
Content-Disposition: attachment;
filename*0="0008-XXX-devel-comment-heap_deform_tuple-slot_deform_tupl.noc";
filename*1="fbot.txt"
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-03-12 15:13 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-08 06:32 [PATCH 02/10] Run 011_crash_recovery.pl with wal_level=minimal Kyotaro Horiguchi <[email protected]>
2022-08-02 07:13 Re: Using each rel as both outer and inner for JOIN_ANTI Richard Guo <[email protected]>
2022-08-09 08:12 ` Re: Using each rel as both outer and inner for JOIN_ANTI Richard Guo <[email protected]>
2022-08-09 10:54 ` Re: Using each rel as both outer and inner for JOIN_ANTI Alvaro Herrera <[email protected]>
2022-08-10 07:57 ` Re: Using each rel as both outer and inner for JOIN_ANTI Richard Guo <[email protected]>
2022-08-10 08:40 ` Re: Using each rel as both outer and inner for JOIN_ANTI Alvaro Herrera <[email protected]>
2022-08-11 02:58 ` Re: Using each rel as both outer and inner for JOIN_ANTI Richard Guo <[email protected]>
2026-03-12 15:13 [PATCH 7/9] no need for palloc0 here -- simple palloc is enough Álvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox