You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by "jiaqizho (via GitHub)" <gi...@apache.org> on 2025/05/27 10:37:38 UTC

[PR] ORCA: deduplicate superset in AndOp when scalar condition has subset [cloudberry]

jiaqizho opened a new pull request, #1126:
URL: https://github.com/apache/cloudberry/pull/1126

   <!-- Thank you for your contribution to Apache Cloudberry (Incubating)! -->
   
   Fixes #ISSUE_Number
   
   ### What does this PR do?
   <!-- Brief overview of the changes, including any major features or fixes -->
   
   ### Type of Change
   - [ ] Bug fix (non-breaking change)
   - [ ] New feature (non-breaking change)
   - [ ] Breaking change (fix or feature with breaking changes)
   - [ ] Documentation update
   
   ### Breaking Changes
   <!-- Remove if not applicable. If yes, explain impact and migration path -->
   
   ### Test Plan
   <!-- How did you test these changes? -->
   - [ ] Unit tests added/updated
   - [ ] Integration tests added/updated
   - [ ] Passed `make installcheck`
   - [ ] Passed `make -C src/test installcheck-cbdb-parallel`
   
   ### Impact
   <!-- Remove sections that don't apply -->
   **Performance:**
   <!-- Any performance implications? -->
   
   **User-facing changes:**
   <!-- Any changes visible to users? -->
   
   **Dependencies:**
   <!-- New dependencies or version changes? -->
   
   ### Checklist
   - [ ] Followed [contribution guide](https://cloudberry.apache.org/contribute/code)
   - [ ] Added/updated documentation
   - [ ] Reviewed code for security implications
   - [ ] Requested review from [cloudberry committers](https://github.com/orgs/apache/teams/cloudberry-committers)
   
   ### Additional Context
   <!-- Any other information that would help reviewers? Remove if none -->
   
   ### CI Skip Instructions
   <!--
   To skip CI builds, add the appropriate CI skip identifier to your PR title.
   The identifier must:
   - Be in square brackets []
   - Include the word "ci" and either "skip" or "no"
   - Only use for documentation-only changes or when absolutely necessary
   -->
   
   ---
   <!-- Join our community:
   - Mailing list: [dev@cloudberry.apache.org](https://lists.apache.org/list.html?dev@cloudberry.apache.org) (subscribe: dev-subscribe@cloudberry.apache.org)
   - Discussions: https://github.com/apache/cloudberry/discussions -->
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org


Re: [PR] [DNM]ORCA: deduplicate superset in AndOp when scalar condition has subset [cloudberry]

Posted by "jiaqizho (via GitHub)" <gi...@apache.org>.
jiaqizho commented on PR #1126:
URL: https://github.com/apache/cloudberry/pull/1126#issuecomment-2917983147

   > Just tried, it doesn't dedup for following query. Is it by purpose?
   > 
   > ```
   > postgres=# explain verbose select * from pt1 where v1 in (select v3 + 1 from pt2) and v1 in (select v3 + 1 from pt2 where v3 = 12);
   >                                                     QUERY PLAN
   > -------------------------------------------------------------------------------------------------------------------
   >  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1293.01 rows=1 width=8)
   >    Output: pt1.v1, pt1.v2
   >    ->  Hash Semi Join  (cost=0.00..1293.01 rows=1 width=8)
   >          Output: pt1.v1, pt1.v2
   >          Hash Cond: (pt1.v1 = ((pt2_1.v3 + 1)))
   >          ->  Hash Semi Join  (cost=0.00..862.01 rows=1 width=8)
   >                Output: pt1.v1, pt1.v2
   >                Hash Cond: (pt1.v1 = ((pt2.v3 + 1)))
   >                ->  Dynamic Seq Scan on public.pt1  (cost=0.00..431.00 rows=1 width=8)
   >                      Output: pt1.v1, pt1.v2
   >                      Number of partitions to scan: 4 (out of 4)
   >                ->  Hash  (cost=431.00..431.00 rows=34 width=4)
   >                      Output: ((pt2.v3 + 1))
   >                      ->  Partition Selector (selector id: $1)  (cost=0.00..431.00 rows=34 width=4)
   >                            Output: ((pt2.v3 + 1))
   >                            ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=34 width=4)
   >                                  Output: ((pt2.v3 + 1))
   >                                  Hash Key: ((pt2.v3 + 1))
   >                                  ->  Dynamic Seq Scan on public.pt2  (cost=0.00..431.00 rows=34 width=4)
   >                                        Output: (pt2.v3 + 1)
   >                                        Number of partitions to scan: 4 (out of 4)
   >          ->  Hash  (cost=431.00..431.00 rows=1 width=4)
   >                Output: ((pt2_1.v3 + 1))
   >                ->  Partition Selector (selector id: $0)  (cost=0.00..431.00 rows=1 width=4)
   >                      Output: ((pt2_1.v3 + 1))
   >                      ->  Redistribute Motion 3:3  (slice3; segments: 3)  (cost=0.00..431.00 rows=1 width=4)
   >                            Output: ((pt2_1.v3 + 1))
   >                            Hash Key: ((pt2_1.v3 + 1))
   >                            ->  Dynamic Seq Scan on public.pt2 pt2_1  (cost=0.00..431.00 rows=1 width=4)
   >                                  Output: (pt2_1.v3 + 1)
   >                                  Number of partitions to scan: 1 (out of 4)
   >                                  Filter: (pt2_1.v3 = 12)
   >  Optimizer: GPORCA
   > (33 rows)
   > ```
   
   Yes, there are no constant expressions involved, otherwise the situation will become complicated. 
   
   If we add support for constant expressions, the number of logical branches will become large.
   
   
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org


Re: [PR] ORCA: deduplicate superset in AndOp when scalar condition has subset [cloudberry]

Posted by "jiaqizho (via GitHub)" <gi...@apache.org>.
jiaqizho commented on code in PR #1126:
URL: https://github.com/apache/cloudberry/pull/1126#discussion_r2149401103


##########
src/backend/gporca/libgpopt/src/operators/CDedupSupersetPreprocessor.cpp:
##########
@@ -0,0 +1,551 @@
+/*-------------------------------------------------------------------------
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+ * CDedupSupersetPreprocessor.cpp
+ *
+ * IDENTIFICATION
+ *	  src/backend/gporca/libgpopt/src/operators/CDedupSupersetPreprocessor.cpp
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "gpopt/operators/CDedupSupersetPreprocessor.h"
+
+#include <limits>
+
+#include "gpopt/operators/CExpression.h"
+#include "gpopt/operators/CLogicalCTEAnchor.h"
+#include "gpopt/operators/CLogicalCTEConsumer.h"
+#include "gpopt/operators/CLogicalCTEProducer.h"
+#include "gpopt/operators/CLogicalDynamicGet.h"
+#include "gpopt/operators/CLogicalGet.h"
+#include "gpopt/operators/CLogicalInnerJoin.h"
+#include "gpopt/operators/CLogicalNAryJoin.h"
+#include "gpopt/operators/CScalarCmp.h"
+#include "gpopt/operators/CScalarIdent.h"
+#include "gpopt/operators/CScalarSubqueryAny.h"
+#include "gpopt/optimizer/COptimizerConfig.h"
+
+using namespace gpopt;
+
+#define CTEID_TO_OID_START 60000
+
+TableDescIdent::TableDescIdent(IMDId *mdid, ULONG sid, ULONG output_colid)
+	: pmdid(mdid), subid(sid), ocolid(output_colid)
+{
+	GPOS_ASSERT(nullptr != mdid);
+}
+
+TableDescIdent::~TableDescIdent()
+{
+	pmdid->Release();
+}
+
+ULONG
+TableDescIdent::HashFunc(const TableDescIdent *ptdi)
+{
+	GPOS_ASSERT(nullptr != ptdi);
+
+	return gpos::CombineHashes(
+		ptdi->pmdid->HashValue(),
+		gpos::CombineHashes(gpos::HashValue(&ptdi->subid),
+							gpos::HashValue(&ptdi->ocolid)));
+}
+
+// equality function
+BOOL
+TableDescIdent::EqualFunc(const TableDescIdent *pltdi,
+						  const TableDescIdent *prtdi)
+{
+	GPOS_ASSERT(nullptr != pltdi && nullptr != prtdi);
+	return pltdi->pmdid->Equals(prtdi->pmdid) && pltdi->subid == prtdi->subid &&
+		   pltdi->ocolid == prtdi->ocolid;
+}
+
+
+// expr is CLogicalGet,CLogicalDynamicGet or CLogicalCTEConsumer ?
+BOOL
+CDedupSupersetPreprocessor::PexprIsLogicalOutput(CExpression *pexpr)
+{
+	GPOS_ASSERT(pexpr);
+	COperator *pop = pexpr->Pop();
+	return pop->Eopid() == COperator::EopLogicalGet ||
+		   pop->Eopid() == COperator::EopLogicalDynamicGet ||
+		   pop->Eopid() == COperator::EopLogicalCTEConsumer;
+}
+
+// Get the meta id from expression
+// The expression must be CLogicalGet/CLogicalDynamicGet/CLogicalCTEConsumer
+// for the CLogicalGet/CLogicalDynamicGet, we can direct get the meta id by table desc.
+// But for the CLogicalCTEConsumer, we will create a meta id from CTEid
+IMDId *
+CDedupSupersetPreprocessor::PexprGetIMDid(CMemoryPool *mp, CExpression *pexpr)
+{
+	GPOS_ASSERT(pexpr);
+	COperator *poper = pexpr->Pop();
+	IMDId *pmdid = nullptr;
+	switch (poper->Eopid())
+	{
+		case COperator::EopLogicalGet:
+		{
+			CLogicalGet *pop_get = CLogicalGet::PopConvert(poper);
+			if (pop_get->Ptabdesc())
+			{
+				pmdid = pop_get->Ptabdesc()->MDId()->Copy(mp);
+			}
+			break;
+		}
+		case COperator::EopLogicalDynamicGet:
+		{
+			CLogicalDynamicGet *pop_dynamicget =
+				CLogicalDynamicGet::PopConvert(poper);
+			if (pop_dynamicget->Ptabdesc())
+			{
+				pmdid = pop_dynamicget->Ptabdesc()->MDId()->Copy(mp);
+			}
+			break;
+		}
+		case COperator::EopLogicalCTEConsumer:
+		{
+			CLogicalCTEConsumer *pop_cc =
+				CLogicalCTEConsumer::PopConvert(poper);
+			pmdid = GPOS_NEW(mp) CMDIdGPDB(
+				IMDId::EmdidGeneral, pop_cc->UlCTEId() + CTEID_TO_OID_START);
+			break;
+		}
+		default:
+			break;
+	}
+
+	return pmdid;
+}
+
+// full the maps with superset expression
+void
+CDedupSupersetPreprocessor::ChildExprFullSuperset(CMemoryPool *mp,
+												  CExpression *pexpr,
+												  TDIToUlongPtrMap *eqcrmaps,
+												  BOOL *dedupulmasks, ULONG ul)
+{
+	CScalarSubqueryAny *pop_subany =
+		CScalarSubqueryAny::PopConvert(pexpr->Pop());
+	CExpression *pexpr_child0 = (*pexpr)[0];
+	IMDId *tdimid = PexprGetIMDid(mp, pexpr_child0);
+	CScalarIdent *pop_ident = CScalarIdent::PopConvert((*pexpr)[1]->Pop());
+	ULONG ulidx_subany;
+
+	if (nullptr == tdimid)
+	{
+		// skip
+		return;
+	}
+	CColRefSet *colrefsets = pexpr_child0->DeriveOutputColumns();
+
+	// The colref(in CScalarSubqueryAny) must in the output
+	(void) colrefsets->ExtractIndex(pop_subany->Pcr(), &ulidx_subany);
+	GPOS_ASSERT(ulidx_subany != gpos::ulong_max);
+
+	TableDescIdent tdikey =
+		TableDescIdent(tdimid, ulidx_subany, pop_ident->Pcr()->Id());
+	ULONG *pul = eqcrmaps->Find(&tdikey);
+	if (!pul)
+	{
+		// new find superset, register it
+		eqcrmaps->Insert(GPOS_NEW(mp) TableDescIdent(tdimid, ulidx_subany,
+													 pop_ident->Pcr()->Id()),
+						 GPOS_NEW(mp) ULONG(ul));
+	}
+	else
+	{
+		// already register the same superset expr, direct remove it
+		//
+		// ex.
+		//   select * from t1,t2 where
+		//   t1.v1 in (select v3 from t2)  <- colref(v3)
+		//   and
+		//   t1.v1 in (select v3 from t2); <- same colref(v3) but id is different
+		dedupulmasks[ul] = false;
+	}
+}
+
+// full the dedups array with subset(simply logicalget)
+void
+CDedupSupersetPreprocessor::ChildExprFullSimplySubset(
+	CMemoryPool *mp, CExpression *pexpr, TDIToUlongPtrMap *eqcrmaps,
+	BOOL *dedupulmasks)

Review Comment:
   nope, 
   
   The main driver in `CDedupSupersetPreprocessor::PexprPreprocess`.
   
   The logical is:
   1. for each `ANDOP` -> use `ChildExprFullSuperset` to init `eqcrmaps`.
   2.  for each `ANDOP` agian -> use `ChildExprFullSimplySubset` and `ChildExprFullJoinSubset` to full the `dedupulmasks`
   3. use the `dedupulmasks`  remove the super set.
   
   And before we call `ChildExprFull*`, we need call the `ChildExprIs*`(you can check the **expression pattern** in this kind of function).
   



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org


Re: [PR] ORCA: deduplicate superset in AndOp when scalar condition has subset [cloudberry]

Posted by "my-ship-it (via GitHub)" <gi...@apache.org>.
my-ship-it commented on code in PR #1126:
URL: https://github.com/apache/cloudberry/pull/1126#discussion_r2113573909


##########
src/backend/gporca/libgpopt/src/operators/CDedupSupersetPreprocessor.cpp:
##########
@@ -0,0 +1,551 @@
+/*-------------------------------------------------------------------------
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+ * CDedupSupersetPreprocessor.cpp
+ *
+ * IDENTIFICATION
+ *	  src/backend/gporca/libgpopt/src/operators/CDedupSupersetPreprocessor.cpp
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "gpopt/operators/CDedupSupersetPreprocessor.h"
+
+#include <limits>
+
+#include "gpopt/operators/CExpression.h"
+#include "gpopt/operators/CLogicalCTEAnchor.h"
+#include "gpopt/operators/CLogicalCTEConsumer.h"
+#include "gpopt/operators/CLogicalCTEProducer.h"
+#include "gpopt/operators/CLogicalDynamicGet.h"
+#include "gpopt/operators/CLogicalGet.h"
+#include "gpopt/operators/CLogicalInnerJoin.h"
+#include "gpopt/operators/CLogicalNAryJoin.h"
+#include "gpopt/operators/CScalarCmp.h"
+#include "gpopt/operators/CScalarIdent.h"
+#include "gpopt/operators/CScalarSubqueryAny.h"
+#include "gpopt/optimizer/COptimizerConfig.h"
+
+using namespace gpopt;
+
+#define CTEID_TO_OID_START 60000
+
+TableDescIdent::TableDescIdent(IMDId *mdid, ULONG sid, ULONG output_colid)
+	: pmdid(mdid), subid(sid), ocolid(output_colid)
+{
+	GPOS_ASSERT(nullptr != mdid);
+}
+
+TableDescIdent::~TableDescIdent()
+{
+	pmdid->Release();
+}
+
+ULONG
+TableDescIdent::HashFunc(const TableDescIdent *ptdi)
+{
+	GPOS_ASSERT(nullptr != ptdi);
+
+	return gpos::CombineHashes(
+		ptdi->pmdid->HashValue(),
+		gpos::CombineHashes(gpos::HashValue(&ptdi->subid),
+							gpos::HashValue(&ptdi->ocolid)));
+}
+
+// equality function
+BOOL
+TableDescIdent::EqualFunc(const TableDescIdent *pltdi,
+						  const TableDescIdent *prtdi)
+{
+	GPOS_ASSERT(nullptr != pltdi && nullptr != prtdi);
+	return pltdi->pmdid->Equals(prtdi->pmdid) && pltdi->subid == prtdi->subid &&
+		   pltdi->ocolid == prtdi->ocolid;
+}
+
+
+// expr is CLogicalGet,CLogicalDynamicGet or CLogicalCTEConsumer ?
+BOOL
+CDedupSupersetPreprocessor::PexprIsLogicalOutput(CExpression *pexpr)
+{
+	GPOS_ASSERT(pexpr);
+	COperator *pop = pexpr->Pop();
+	return pop->Eopid() == COperator::EopLogicalGet ||
+		   pop->Eopid() == COperator::EopLogicalDynamicGet ||
+		   pop->Eopid() == COperator::EopLogicalCTEConsumer;
+}
+
+// Get the meta id from expression
+// The expression must be CLogicalGet/CLogicalDynamicGet/CLogicalCTEConsumer
+// for the CLogicalGet/CLogicalDynamicGet, we can direct get the meta id by table desc.
+// But for the CLogicalCTEConsumer, we will create a meta id from CTEid
+IMDId *
+CDedupSupersetPreprocessor::PexprGetIMDid(CMemoryPool *mp, CExpression *pexpr)
+{
+	GPOS_ASSERT(pexpr);
+	COperator *poper = pexpr->Pop();
+	IMDId *pmdid = nullptr;
+	switch (poper->Eopid())
+	{
+		case COperator::EopLogicalGet:
+		{
+			CLogicalGet *pop_get = CLogicalGet::PopConvert(poper);
+			if (pop_get->Ptabdesc())
+			{
+				pmdid = pop_get->Ptabdesc()->MDId()->Copy(mp);
+			}
+			break;
+		}
+		case COperator::EopLogicalDynamicGet:
+		{
+			CLogicalDynamicGet *pop_dynamicget =
+				CLogicalDynamicGet::PopConvert(poper);
+			if (pop_dynamicget->Ptabdesc())
+			{
+				pmdid = pop_dynamicget->Ptabdesc()->MDId()->Copy(mp);
+			}
+			break;
+		}
+		case COperator::EopLogicalCTEConsumer:
+		{
+			CLogicalCTEConsumer *pop_cc =
+				CLogicalCTEConsumer::PopConvert(poper);
+			pmdid = GPOS_NEW(mp) CMDIdGPDB(
+				IMDId::EmdidGeneral, pop_cc->UlCTEId() + CTEID_TO_OID_START);
+			break;
+		}
+		default:
+			break;
+	}
+
+	return pmdid;
+}
+
+// full the maps with superset expression
+void
+CDedupSupersetPreprocessor::ChildExprFullSuperset(CMemoryPool *mp,
+												  CExpression *pexpr,
+												  TDIToUlongPtrMap *eqcrmaps,
+												  BOOL *dedupulmasks, ULONG ul)
+{
+	CScalarSubqueryAny *pop_subany =
+		CScalarSubqueryAny::PopConvert(pexpr->Pop());
+	CExpression *pexpr_child0 = (*pexpr)[0];
+	IMDId *tdimid = PexprGetIMDid(mp, pexpr_child0);
+	CScalarIdent *pop_ident = CScalarIdent::PopConvert((*pexpr)[1]->Pop());
+	ULONG ulidx_subany;
+
+	if (nullptr == tdimid)
+	{
+		// skip
+		return;
+	}
+	CColRefSet *colrefsets = pexpr_child0->DeriveOutputColumns();
+
+	// The colref(in CScalarSubqueryAny) must in the output
+	(void) colrefsets->ExtractIndex(pop_subany->Pcr(), &ulidx_subany);
+	GPOS_ASSERT(ulidx_subany != gpos::ulong_max);
+
+	TableDescIdent tdikey =
+		TableDescIdent(tdimid, ulidx_subany, pop_ident->Pcr()->Id());
+	ULONG *pul = eqcrmaps->Find(&tdikey);
+	if (!pul)
+	{
+		// new find superset, register it
+		eqcrmaps->Insert(GPOS_NEW(mp) TableDescIdent(tdimid, ulidx_subany,
+													 pop_ident->Pcr()->Id()),
+						 GPOS_NEW(mp) ULONG(ul));
+	}
+	else
+	{
+		// already register the same superset expr, direct remove it
+		//
+		// ex.
+		//   select * from t1,t2 where
+		//   t1.v1 in (select v3 from t2)  <- colref(v3)
+		//   and
+		//   t1.v1 in (select v3 from t2); <- same colref(v3) but id is different
+		dedupulmasks[ul] = false;
+	}
+}
+
+// full the dedups array with subset(simply logicalget)
+void
+CDedupSupersetPreprocessor::ChildExprFullSimplySubset(
+	CMemoryPool *mp, CExpression *pexpr, TDIToUlongPtrMap *eqcrmaps,
+	BOOL *dedupulmasks)

Review Comment:
   The function is similar to ChildExprFullSuperset above. Have we already finished dedup in ChildExprFullSuperset?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org


Re: [PR] [DNM]ORCA: deduplicate superset in AndOp when scalar condition has subset [cloudberry]

Posted by "my-ship-it (via GitHub)" <gi...@apache.org>.
my-ship-it commented on PR #1126:
URL: https://github.com/apache/cloudberry/pull/1126#issuecomment-2915864819

   Just tried, it doesn't dedup for following query. Is it by purpose?
   
   ```
   postgres=# explain verbose select * from pt1 where v1 in (select v3 + 1 from pt2) and v1 in (select v3 + 1 from pt2 where v3 = 12);
                                                       QUERY PLAN
   -------------------------------------------------------------------------------------------------------------------
    Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1293.01 rows=1 width=8)
      Output: pt1.v1, pt1.v2
      ->  Hash Semi Join  (cost=0.00..1293.01 rows=1 width=8)
            Output: pt1.v1, pt1.v2
            Hash Cond: (pt1.v1 = ((pt2_1.v3 + 1)))
            ->  Hash Semi Join  (cost=0.00..862.01 rows=1 width=8)
                  Output: pt1.v1, pt1.v2
                  Hash Cond: (pt1.v1 = ((pt2.v3 + 1)))
                  ->  Dynamic Seq Scan on public.pt1  (cost=0.00..431.00 rows=1 width=8)
                        Output: pt1.v1, pt1.v2
                        Number of partitions to scan: 4 (out of 4)
                  ->  Hash  (cost=431.00..431.00 rows=34 width=4)
                        Output: ((pt2.v3 + 1))
                        ->  Partition Selector (selector id: $1)  (cost=0.00..431.00 rows=34 width=4)
                              Output: ((pt2.v3 + 1))
                              ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=34 width=4)
                                    Output: ((pt2.v3 + 1))
                                    Hash Key: ((pt2.v3 + 1))
                                    ->  Dynamic Seq Scan on public.pt2  (cost=0.00..431.00 rows=34 width=4)
                                          Output: (pt2.v3 + 1)
                                          Number of partitions to scan: 4 (out of 4)
            ->  Hash  (cost=431.00..431.00 rows=1 width=4)
                  Output: ((pt2_1.v3 + 1))
                  ->  Partition Selector (selector id: $0)  (cost=0.00..431.00 rows=1 width=4)
                        Output: ((pt2_1.v3 + 1))
                        ->  Redistribute Motion 3:3  (slice3; segments: 3)  (cost=0.00..431.00 rows=1 width=4)
                              Output: ((pt2_1.v3 + 1))
                              Hash Key: ((pt2_1.v3 + 1))
                              ->  Dynamic Seq Scan on public.pt2 pt2_1  (cost=0.00..431.00 rows=1 width=4)
                                    Output: (pt2_1.v3 + 1)
                                    Number of partitions to scan: 1 (out of 4)
                                    Filter: (pt2_1.v3 = 12)
    Optimizer: GPORCA
   (33 rows)
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org


Re: [PR] ORCA: deduplicate superset in AndOp when scalar condition has subset [cloudberry]

Posted by "my-ship-it (via GitHub)" <gi...@apache.org>.
my-ship-it merged PR #1126:
URL: https://github.com/apache/cloudberry/pull/1126


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org