Recognize that IN subqueries return already-unique results if they use

UNION/INTERSECT/EXCEPT (without ALL).  This adds on to the previous
optimization for subqueries using DISTINCT.

1 files changed, 35 insertions, 6 deletions

diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 5fe12f2b6d5..059685e76aa 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/util/pathnode.c,v 1.99 2004/01/05 23:39:54 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/util/pathnode.c,v 1.100 2004/01/19 03:49:41 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -33,6 +33,7 @@
 #include "utils/syscache.h"
 
 
+static bool is_distinct_query(Query *query);
 static bool hash_safe_tlist(List *tlist);
 
 
@@ -553,16 +554,14 @@ create_unique_path(Query *root, RelOptInfo *rel, Path *subpath)
 	pathnode->subpath = subpath;
 
 	/*
-	 * If the input is a subquery that uses DISTINCT, we don't need to do
-	 * anything; its output is already unique.  (Are there any other cases
-	 * in which we can easily prove the input must be distinct?)
+	 * If the input is a subquery whose output must be unique already,
+	 * we don't need to do anything.
 	 */
 	if (rel->rtekind == RTE_SUBQUERY)
 	{
 		RangeTblEntry *rte = rt_fetch(rel->relid, root->rtable);
-		Query	   *subquery = rte->subquery;
 
-		if (has_distinct_clause(subquery))
+		if (is_distinct_query(rte->subquery))
 		{
 			pathnode->umethod = UNIQUE_PATH_NOOP;
 			pathnode->rows = rel->rows;
@@ -668,6 +667,36 @@ create_unique_path(Query *root, RelOptInfo *rel, Path *subpath)
 }
 
 /*
+ * is_distinct_query - does query never return duplicate rows?
+ */
+static bool
+is_distinct_query(Query *query)
+{
+	/* DISTINCT (but not DISTINCT ON) guarantees uniqueness */
+	if (has_distinct_clause(query))
+		return true;
+
+	/* UNION, INTERSECT, EXCEPT guarantee uniqueness, except with ALL */
+	if (query->setOperations)
+	{
+		SetOperationStmt *topop = (SetOperationStmt *) query->setOperations;
+
+		Assert(IsA(topop, SetOperationStmt));
+		Assert(topop->op != SETOP_NONE);
+
+		if (!topop->all)
+			return true;
+	}
+
+	/*
+	 * XXX Are there any other cases in which we can easily see the result
+	 * must be distinct?
+	 */
+
+	return false;
+}
+
+/*
  * hash_safe_tlist - can datatypes of given tlist be hashed?
  *
  * We assume hashed aggregation will work if the datatype's equality operator