From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Optimization issue of branching UNION ALL
Date: Wed, 21 Dec 2022 09:14:02 +0500
Message-ID: <703c09a2-08f3-d2ec-b33d-dbecd62428b8@postgrespro.ru> (raw)
Hi,
Client report on a corner case have shown up possible minor
non-optimality in procedure of transformation of simple UNION ALL
statement tree.
Complaint is about auto-generated query with 1E4 simple union all's (see
t.sh to generate a demo script). The reason: in REL_11_STABLE it is
planned and executed in a second, but REL_12_STABLE and beyond makes
matters worse: planning of such a query needs tons of gigabytes of RAM.
Superficial study revealed possibly unnecessary operations that could be
avoided:
1. Walking across a query by calling substitute_phv_relids() even if
lastPHId shows that no one phv is presented.
2. Iterative passes along the append_rel_list for replacing vars in the
translated_vars field. I can't grasp real necessity of passing all the
append_rel_list during flattening of an union all leaf subquery. No one
can reference this leaf, isn't it?
In attachment you can see some sketch that reduces a number of planner
cycles/copyings.
--
Regards
Andrey Lepikhov
Postgres Professional
Attachments:
t.sh (application/x-shellscript, 187B) downloadunion_all_optimization.patch (text/x-patch, 3.7K) downloaddiff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.cindex 08a73fb9d86..3739e3fe7ba 100644--- a/src/backend/optimizer/prep/prepjointree.c+++ b/src/backend/optimizer/prep/prepjointree.c@@ -131,7 +131,7 @@ static bool find_dependent_phvs_in_jointree(PlannerInfo *root,
Node *node, int varno);
static void substitute_phv_relids(Node *node,
int varno, Relids subrelids);
-static void fix_append_rel_relids(List *append_rel_list, int varno,+static void fix_append_rel_relids(PlannerInfo *root, int varno,
Relids subrelids);
static Node *find_jointree_node_for_rel(Node *jtnode, int relid);
@@ -1156,8 +1156,9 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
Relids subrelids;
subrelids = get_relids_in_jointree((Node *) subquery->jointree, false);
- substitute_phv_relids((Node *) parse, varno, subrelids);- fix_append_rel_relids(root->append_rel_list, varno, subrelids);+ if (root->glob->lastPHId != 0)+ substitute_phv_relids((Node *) parse, varno, subrelids);+ fix_append_rel_relids(root, varno, subrelids);
}
/*
@@ -2064,17 +2065,25 @@ perform_pullup_replace_vars(PlannerInfo *root,
* use PHVs for safety. (This analysis could be made tighter but it seems
* unlikely to be worth much trouble.)
*/
- foreach(lc, root->append_rel_list)+ if (containing_appendrel)
{
- AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);- bool save_need_phvs = rvcontext->need_phvs;+ bool save_need_phvs = rvcontext->need_phvs;- if (appinfo == containing_appendrel)- rvcontext->need_phvs = false;- appinfo->translated_vars = (List *)- pullup_replace_vars((Node *) appinfo->translated_vars, rvcontext);+ rvcontext->need_phvs = false;+ containing_appendrel->translated_vars = (List *)+ pullup_replace_vars((Node *) containing_appendrel->translated_vars,+ rvcontext);
rvcontext->need_phvs = save_need_phvs;
}
+ else+ {+ foreach(lc, root->append_rel_list)+ {+ AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);+ appinfo->translated_vars = (List *)+ pullup_replace_vars((Node *) appinfo->translated_vars, rvcontext);+ }+ }
/*
* Replace references in the joinaliasvars lists of join RTEs.
@@ -3273,7 +3282,7 @@ remove_result_refs(PlannerInfo *root, int varno, Node *newjtloc)
subrelids = get_relids_in_jointree(newjtloc, false);
Assert(!bms_is_empty(subrelids));
substitute_phv_relids((Node *) root->parse, varno, subrelids);
- fix_append_rel_relids(root->append_rel_list, varno, subrelids);+ fix_append_rel_relids(root, varno, subrelids);
}
/*
@@ -3492,7 +3501,7 @@ substitute_phv_relids(Node *node, int varno, Relids subrelids)
* We assume we may modify the AppendRelInfo nodes in-place.
*/
static void
-fix_append_rel_relids(List *append_rel_list, int varno, Relids subrelids)+fix_append_rel_relids(PlannerInfo *root, int varno, Relids subrelids)
{
ListCell *l;
int subvarno = -1;
@@ -3503,7 +3512,7 @@ fix_append_rel_relids(List *append_rel_list, int varno, Relids subrelids)
* AppendRelInfo nodes refer to it. So compute it on first use. Note that
* bms_singleton_member will complain if set is not singleton.
*/
- foreach(l, append_rel_list)+ foreach(l, root->append_rel_list)
{
AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
@@ -3518,8 +3527,9 @@ fix_append_rel_relids(List *append_rel_list, int varno, Relids subrelids)
}
/* Also fix up any PHVs in its translated vars */
- substitute_phv_relids((Node *) appinfo->translated_vars,- varno, subrelids);+ if (root->glob->lastPHId != 0)+ substitute_phv_relids((Node *) appinfo->translated_vars,+ varno, subrelids);
}
}
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.lepikhov@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: Optimization issue of branching UNION ALL
In-Reply-To: <703c09a2-08f3-d2ec-b33d-dbecd62428b8@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox