Re: Using indices for UNION.

Sorry, I missed to attach file.

> This is cont'd from CF3.
> 
> http://www.postgresql.org/message-id/20131122.165927.27412386.horiguchi.kyotaro@lab.ntt.co.jp
> 
> The issue in brief is that UNION is never flattened differently
> to UNION ALL so UNION cannot make use of index scan even if
> usable.
> 
> This patch flattens UNION likewise currently did for UNION ALL.
> 
> This patch needs another 'UNION ALL' patch and further gain with
> even another 'Widening application of indices' patch. ('Ready for
> Commit' now in CF3..)
> 
> http://www.postgresql.org/message-id/20140114.180447.145186052.horiguchi.kyotaro@lab.ntt.co.jp
> http://www.postgresql.org/message-id/20140114.180810.122352231.horiguchi.kyotaro@lab.ntt.co.jp
> 
> 
> You can see the detailed outlines in the message at here,
> 
> http://www.postgresql.org/message-id/20131031.194251.12577697.horiguchi.kyotaro@lab.ntt.co.jp
> 
> The attached patche is rebased to current 9.4dev HEAD and make
> check at the topmost directory and src/test/isolation are passed
> without error.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 1da4b2f..e89f8b3 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -353,13 +353,14 @@ subquery_planner(PlannerGlobal *glob, Query *parse,        pull_up_subqueries(root, (Node *)
parse->jointree);   /*
 
-     * If this is a simple UNION ALL query, flatten it into an appendrel. We
-     * do this now because it requires applying pull_up_subqueries to the leaf
-     * queries of the UNION ALL, which weren't touched above because they
-     * weren't referenced by the jointree (they will be after we do this).
+     * If this is a simple UNION/UNION ALL query, flatten it into an
+     * appendrel. We do this now because it requires applying
+     * pull_up_subqueries to the leaf queries of the UNION/UNION ALL, which
+     * weren't touched above because they weren't referenced by the jointree
+     * (they will be after we do this).     */    if (parse->setOperations)
-        flatten_simple_union_all(root);
+        flatten_simple_union(root);    /*     * Detect whether any rangetable entries are RTE_JOIN kind; if not, we
can
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 1c6083b..04bba48 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -32,6 +32,7 @@#include "optimizer/subselect.h"#include "optimizer/tlist.h"#include "parser/parse_relation.h"
+#include "parser/parse_clause.h"#include "parser/parsetree.h"#include "rewrite/rewriteManip.h"
@@ -81,8 +82,8 @@ static void make_setop_translation_list(Query *query, Index newvarno,static bool
is_simple_subquery(Query*subquery, RangeTblEntry *rte,                   JoinExpr *lowest_outer_join);static bool
is_simple_union_all(Query*subquery);
 
-static bool is_simple_union_all_recurse(Node *setOp, Query *setOpQuery,
-                            List *colTypes);
+static bool is_simple_union_recurse(SetOperationStmt *topop, Node *setOp,
+                                    Query *setOpQuery, List *colTypes);static bool is_safe_append_member(Query
*subquery);staticbool jointree_contains_lateral_outer_refs(Node *jtnode, bool restricted,
     Relids safe_upper_varnos);
 
@@ -1440,12 +1441,14 @@ is_simple_union_all(Query *subquery)        return false;    /* Recursively check the tree of
setoperations */
 
-    return is_simple_union_all_recurse((Node *) topop, subquery,
-                                       topop->colTypes);
+    if (topop->op != SETOP_UNION || !topop->all) return false;
+    return is_simple_union_recurse(topop, (Node *) topop, subquery,
+                                   topop->colTypes);}static bool
-is_simple_union_all_recurse(Node *setOp, Query *setOpQuery, List *colTypes)
+is_simple_union_recurse(SetOperationStmt *topop, Node *setOp,
+                        Query *setOpQuery, List *colTypes){    if (IsA(setOp, RangeTblRef))    {
@@ -1463,13 +1466,16 @@ is_simple_union_all_recurse(Node *setOp, Query *setOpQuery, List *colTypes)    {
SetOperationStmt*op = (SetOperationStmt *) setOp;
 
-        /* Must be UNION ALL */
-        if (op->op != SETOP_UNION || !op->all)
+        /* All SetOps under topop are UNION and ->all is same to topop */
+        if (op->op != SETOP_UNION || op->all != topop->all)            return false;        /* Recurse to check inputs
*/
-        return is_simple_union_all_recurse(op->larg, setOpQuery, colTypes) &&
-            is_simple_union_all_recurse(op->rarg, setOpQuery, colTypes);
+        return
+            is_simple_union_recurse(topop, op->larg,
+                                    setOpQuery, colTypes) &&
+            is_simple_union_recurse(topop, op->rarg,
+                                    setOpQuery, colTypes);    }    else    {
@@ -1908,23 +1914,22 @@ pullup_replace_vars_subquery(Query *query,                                           NULL);}
-/* * flatten_simple_union_all
- *        Try to optimize top-level UNION ALL structure into an appendrel
+ *        Try to optimize top-level UNION/UNION ALL structure into an appendrel *
- * If a query's setOperations tree consists entirely of simple UNION ALL
- * operations, flatten it into an append relation, which we can process more
- * intelligently than the general setops case.    Otherwise, do nothing.
+ * If a query's setOperations tree consists entirely of simple UNION and UNION
+ * ALL operations, flatten it into an append relation, which we can process
+ * more intelligently than the general setops case. Otherwise, do nothing. * * In most cases, this can succeed only
fora top-level query, because for a * subquery in FROM, the parent query's invocation of pull_up_subqueries would
 
- * already have flattened the UNION via pull_up_simple_union_all.  But there
+ * already have flattened the UNION ALL via pull_up_simple_union_all.  But there * are a few cases we can support here
butnot in that code path, for example * when the subquery also contains ORDER BY. */void
 
-flatten_simple_union_all(PlannerInfo *root)
+flatten_simple_union(PlannerInfo *root){    Query       *parse = root->parse;    SetOperationStmt *topop;
@@ -1944,10 +1949,18 @@ flatten_simple_union_all(PlannerInfo *root)        return;    /*
-     * Recursively check the tree of set operations.  If not all UNION ALL
+     * If topop is not UNION (not likey), punt. Also for UNION(not ALL)
+     * without sortClause or already having distinctClause.
+     */
+    if (topop->op != SETOP_UNION ||
+        (!topop->all && (!parse->sortClause || parse->distinctClause )))
+        return;
+
+    /*
+     * Recursively check the tree of set operations.  If not all UNION(ALL)     * with identical column types, punt.
 */
 
-    if (!is_simple_union_all_recurse((Node *) topop, parse, topop->colTypes))
+    if (!is_simple_union_recurse(topop, (Node *) topop, parse, topop->colTypes))        return;    /*
@@ -1995,6 +2008,16 @@ flatten_simple_union_all(PlannerInfo *root)    parse->setOperations = NULL;    /*
+     * We can create distinctClause using transformDistinctClause() with
+     * pstate == NULL.
+     */
+    if (!topop->all)
+        parse->distinctClause =
+            transformDistinctClause(NULL,
+                                    &parse->targetList, parse->sortClause,
+                                    false);
+
+    /*     * Build AppendRelInfo information, and apply pull_up_subqueries to the     * leaf queries of the UNION ALL.
(We must do that now because they     * weren't previously referenced by the jointree, and so were missed by
 
diff --git a/src/include/optimizer/prep.h b/src/include/optimizer/prep.h
index 0934e63..32b3bf4 100644
--- a/src/include/optimizer/prep.h
+++ b/src/include/optimizer/prep.h
@@ -24,7 +24,7 @@extern void pull_up_sublinks(PlannerInfo *root);extern void inline_set_returning_functions(PlannerInfo
*root);externNode *pull_up_subqueries(PlannerInfo *root, Node *jtnode);
 
-extern void flatten_simple_union_all(PlannerInfo *root);
+extern void flatten_simple_union(PlannerInfo *root);extern void reduce_outer_joins(PlannerInfo *root);extern Relids
get_relids_in_jointree(Node*jtnode, bool include_joins);extern Relids get_relids_for_join(PlannerInfo *root, int
joinrelid);