Re: speeding up planning with partitions - Mailing list pgsql-hackers

One thing that I intentionally left out of the committed patch was changes
to stop short of scanning the whole simple_rel_array when looking only for
baserels.  I thought that had been done in a rather piecemeal fashion
and it'd be better to address it holistically, which I've now done in the
attached proposed patch.

This probably makes little if any difference in the test cases we've
mostly focused on in this thread, since there wouldn't be very many
otherrels anyway now that we don't create them for pruned partitions.
However, in a case where there's a lot of partitions that we can't prune,
this could be useful.

I have not done any performance testing to see if this is actually
worth the trouble, though.  Anybody want to do that?

            regards, tom lane

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 727da33..7a9aa12 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -157,7 +157,7 @@ make_one_rel(PlannerInfo *root, List *joinlist)
      * Construct the all_baserels Relids set.
      */
     root->all_baserels = NULL;
-    for (rti = 1; rti < root->simple_rel_array_size; rti++)
+    for (rti = 1; rti <= root->last_base_relid; rti++)
     {
         RelOptInfo *brel = root->simple_rel_array[rti];

@@ -290,7 +290,7 @@ set_base_rel_sizes(PlannerInfo *root)
 {
     Index        rti;

-    for (rti = 1; rti < root->simple_rel_array_size; rti++)
+    for (rti = 1; rti <= root->last_base_relid; rti++)
     {
         RelOptInfo *rel = root->simple_rel_array[rti];
         RangeTblEntry *rte;
@@ -333,7 +333,7 @@ set_base_rel_pathlists(PlannerInfo *root)
 {
     Index        rti;

-    for (rti = 1; rti < root->simple_rel_array_size; rti++)
+    for (rti = 1; rti <= root->last_base_relid; rti++)
     {
         RelOptInfo *rel = root->simple_rel_array[rti];

@@ -1994,7 +1994,7 @@ has_multiple_baserels(PlannerInfo *root)
     int            num_base_rels = 0;
     Index        rti;

-    for (rti = 1; rti < root->simple_rel_array_size; rti++)
+    for (rti = 1; rti <= root->last_base_relid; rti++)
     {
         RelOptInfo *brel = root->simple_rel_array[rti];

diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 61b5b11..723643c 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -828,11 +828,11 @@ generate_base_implied_equalities(PlannerInfo *root)
      * This is also a handy place to mark base rels (which should all exist by
      * now) with flags showing whether they have pending eclass joins.
      */
-    for (rti = 1; rti < root->simple_rel_array_size; rti++)
+    for (rti = 1; rti <= root->last_base_relid; rti++)
     {
         RelOptInfo *brel = root->simple_rel_array[rti];

-        if (brel == NULL)
+        if (brel == NULL || brel->reloptkind != RELOPT_BASEREL)
             continue;

         brel->has_eclass_joins = has_relevant_eclass_joinclause(root, brel);
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 9798dca..c5459b6 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -145,7 +145,7 @@ add_other_rels_to_query(PlannerInfo *root)
 {
     int            rti;

-    for (rti = 1; rti < root->simple_rel_array_size; rti++)
+    for (rti = 1; rti <= root->last_base_relid; rti++)
     {
         RelOptInfo *rel = root->simple_rel_array[rti];
         RangeTblEntry *rte = root->simple_rte_array[rti];
@@ -312,7 +312,7 @@ find_lateral_references(PlannerInfo *root)
     /*
      * Examine all baserels (the rel array has been set up by now).
      */
-    for (rti = 1; rti < root->simple_rel_array_size; rti++)
+    for (rti = 1; rti <= root->last_base_relid; rti++)
     {
         RelOptInfo *brel = root->simple_rel_array[rti];

@@ -460,7 +460,7 @@ create_lateral_join_info(PlannerInfo *root)
     /*
      * Examine all baserels (the rel array has been set up by now).
      */
-    for (rti = 1; rti < root->simple_rel_array_size; rti++)
+    for (rti = 1; rti <= root->last_base_relid; rti++)
     {
         RelOptInfo *brel = root->simple_rel_array[rti];
         Relids        lateral_relids;
@@ -580,7 +580,7 @@ create_lateral_join_info(PlannerInfo *root)
      * The outer loop considers each baserel, and propagates its lateral
      * dependencies to those baserels that have a lateral dependency on it.
      */
-    for (rti = 1; rti < root->simple_rel_array_size; rti++)
+    for (rti = 1; rti <= root->last_base_relid; rti++)
     {
         RelOptInfo *brel = root->simple_rel_array[rti];
         Relids        outer_lateral_relids;
@@ -595,7 +595,7 @@ create_lateral_join_info(PlannerInfo *root)
             continue;

         /* else scan all baserels */
-        for (rti2 = 1; rti2 < root->simple_rel_array_size; rti2++)
+        for (rti2 = 1; rti2 <= root->last_base_relid; rti2++)
         {
             RelOptInfo *brel2 = root->simple_rel_array[rti2];

@@ -614,7 +614,7 @@ create_lateral_join_info(PlannerInfo *root)
      * with the set of relids of rels that reference it laterally (possibly
      * indirectly) --- that is, the inverse mapping of lateral_relids.
      */
-    for (rti = 1; rti < root->simple_rel_array_size; rti++)
+    for (rti = 1; rti <= root->last_base_relid; rti++)
     {
         RelOptInfo *brel = root->simple_rel_array[rti];
         Relids        lateral_relids;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 3a1b846..26aa7fa 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1215,6 +1215,7 @@ inheritance_planner(PlannerInfo *root)
     List       *final_rtable = NIL;
     List       *final_rowmarks = NIL;
     int            save_rel_array_size = 0;
+    int            save_last_base_relid = 0;
     RelOptInfo **save_rel_array = NULL;
     AppendRelInfo **save_append_rel_array = NULL;
     List       *subpaths = NIL;
@@ -1664,6 +1665,8 @@ inheritance_planner(PlannerInfo *root)
                 subroot->simple_rel_array[rti] = brel;
         }
         save_rel_array_size = subroot->simple_rel_array_size;
+        save_last_base_relid = Max(save_last_base_relid,
+                                   subroot->last_base_relid);
         save_rel_array = subroot->simple_rel_array;
         save_append_rel_array = subroot->append_rel_array;

@@ -1741,6 +1744,7 @@ inheritance_planner(PlannerInfo *root)
          */
         parse->rtable = final_rtable;
         root->simple_rel_array_size = save_rel_array_size;
+        root->last_base_relid = save_last_base_relid;
         root->simple_rel_array = save_rel_array;
         root->append_rel_array = save_append_rel_array;

diff --git a/src/backend/optimizer/util/orclauses.c b/src/backend/optimizer/util/orclauses.c
index b671581..c04c91d 100644
--- a/src/backend/optimizer/util/orclauses.c
+++ b/src/backend/optimizer/util/orclauses.c
@@ -78,7 +78,7 @@ extract_restriction_or_clauses(PlannerInfo *root)
     Index        rti;

     /* Examine each baserel for potential join OR clauses */
-    for (rti = 1; rti < root->simple_rel_array_size; rti++)
+    for (rti = 1; rti <= root->last_base_relid; rti++)
     {
         RelOptInfo *rel = root->simple_rel_array[rti];
         ListCell   *lc;
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 272e2eb..a79c738 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -82,6 +82,9 @@ setup_simple_rel_arrays(PlannerInfo *root)
     root->simple_rel_array = (RelOptInfo **)
         palloc0(root->simple_rel_array_size * sizeof(RelOptInfo *));

+    /* obviously, there are no RELOPT_BASEREL entries yet */
+    root->last_base_relid = 0;
+
     /* simple_rte_array is an array equivalent of the rtable list */
     root->simple_rte_array = (RangeTblEntry **)
         palloc0(root->simple_rel_array_size * sizeof(RangeTblEntry *));
@@ -348,6 +351,14 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
     /* Save the finished struct in the query's simple_rel_array */
     root->simple_rel_array[relid] = rel;

+    /*
+     * Track the highest index of any BASEREL entry.  This is useful since
+     * many places scan the simple_rel_array for baserels and don't care about
+     * otherrels; they can stop before scanning otherrels.
+     */
+    if (rel->reloptkind == RELOPT_BASEREL)
+        root->last_base_relid = Max(root->last_base_relid, relid);
+
     return rel;
 }

diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 88c8973..fcccffc 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -201,6 +201,7 @@ struct PlannerInfo
      */
     struct RelOptInfo **simple_rel_array;    /* All 1-rel RelOptInfos */
     int            simple_rel_array_size;    /* allocated size of array */
+    int            last_base_relid;    /* index of last baserel in array */

     /*
      * simple_rte_array is the same length as simple_rel_array and holds