Sorry, I missed to attach file.
> This is cont'd from CF3.
>
> http://www.postgresql.org/message-id/20131122.165927.27412386.horiguchi.kyot...@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.kyot...@lab.ntt.co.jp
> http://www.postgresql.org/message-id/20140114.180810.122352231.horiguchi.kyot...@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.kyot...@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);
static bool 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 set operations */
- 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 for a 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 but not 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);
extern Node *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);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
