Thread: [PATCH] Document heuristics for parameterized paths

Hi,

A small patch to the documentation about how to reduce the number of
parameterized paths, because it kept me searching for a long time :-)
(The code this documents is in add_paths_to_joinrel(), the loop
foreach(lc, root->join_info_list).)


diff --git a/src/backend/optimizer/README b/src/backend/optimizer/README
index 41c120e0cd..79e270188d 100644
--- a/src/backend/optimizer/README
+++ b/src/backend/optimizer/README
@@ -863,6 +863,19 @@ that.  An exception occurs for parameterized paths for the RHS relation of
 a SEMI or ANTI join: in those cases, we can stop the inner scan after the
 first match, so it's primarily startup not total cost that we care about.
 
+Furthermore, join trees involving parameterized paths are kept as left-deep
+as possible; for nested loops consisting of inner joins only, bushy plans
+are equivalent to left-deep ones, so keeping bushy plans is only a waste.
+This is enforced by refusing to join parameterized paths together unless
+the parameterization is resolved, *or* the remaining parameterization is
+one that must cannot be delayed right away (because of outer join
+restrictions).  This ensures that we do not keep around large subplans that
+are parameterized on a whole host of external relations, without losing
+any plans.  An exception is that we are allowed to keep a parameterization
+around if we *partially* resolve it, i.e., we had a multi-part index and
+resolved only one table from it.  This is known as the "star-schema"
+exception.
+
 
 LATERAL subqueries
 ------------------

/* Steinar */
-- 
Homepage: https://www.sesse.net/

On Mon, 6 Dec 2021 at 13:01, Steinar H. Gunderson
<steinar+postgres@gunderson.no> wrote:
>
> +one that must cannot be delayed right away (because of outer join

must cannot?

-- 
greg

On Wed, 15 Dec 2021 at 23:22, Greg Stark <stark@mit.edu> wrote:
>
> On Mon, 6 Dec 2021 at 13:01, Steinar H. Gunderson
> <steinar+postgres@gunderson.no> wrote:
> >
> > +one that must cannot be delayed right away (because of outer join
>
> must cannot?

Actually on further reading... "delayed right away"?

-- 
greg

On Wed, Dec 15, 2021 at 11:23:12PM -0500, Greg Stark wrote:
>>> +one that must cannot be delayed right away (because of outer join
>> must cannot?
> Actually on further reading... "delayed right away"?

There are two ways of writing this sentence, and I see that I tried to use
both of them in the same sentence :-) What about something like the
following?

  This is enforced by refusing to join parameterized paths together unless
  the parameterization is resolved, *or* the remaining parameterization is
  one that cannot be resolved right away (because of outer join restrictions). 

/* Steinar */
-- 
Homepage: https://www.sesse.net/