Revise parameterized-path mechanism to fix assorted issues.

This patch adjusts the treatment of parameterized paths so that all paths
with the same parameterization (same set of required outer rels) for the
same relation will have the same rowcount estimate.  We cache the rowcount
estimates to ensure that property, and hopefully save a few cycles too.
Doing this makes it practical for add_path_precheck to operate without
a rowcount estimate: it need only assume that paths with different
parameterizations never dominate each other, which is close enough to
true anyway for coarse filtering, because normally a more-parameterized
path should yield fewer rows thanks to having more join clauses to apply.

In add_path, we do the full nine yards of comparing rowcount estimates
along with everything else, so that we can discard parameterized paths that
don't actually have an advantage.  This fixes some issues I'd found with
add_path rejecting parameterized paths on the grounds that they were more
expensive than not-parameterized ones, even though they yielded many fewer
rows and hence would be cheaper once subsequent joining was considered.

To make the same-rowcounts assumption valid, we have to require that any
parameterized path enforce *all* join clauses that could be obtained from
the particular set of outer rels, even if not all of them are useful for
indexing.  This is required at both base scans and joins.  It's a good
thing anyway since the net impact is that join quals are checked at the
lowest practical level in the join tree.  Hence, discard the original
rather ad-hoc mechanism for choosing parameterization joinquals, and build
a better one that has a more principled rule for when clauses can be moved.
The original rule was actually buggy anyway for lack of knowledge about
which relations are part of an outer join's outer side; getting this right
requires adding an outer_relids field to RestrictInfo.

1 files changed, 70 insertions, 0 deletions

diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index d8f20e8ce2c..92a64c8dba2 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -971,6 +971,76 @@ drop cascades to table inhts
 drop cascades to table inht3
 drop cascades to table inht4
 --
+-- Test parameterized append plans for inheritance trees
+--
+create temp table patest0 (id, x) as
+  select x, x from generate_series(0,1000) x;
+create temp table patest1() inherits (patest0);
+insert into patest1
+  select x, x from generate_series(0,1000) x;
+create temp table patest2() inherits (patest0);
+insert into patest2
+  select x, x from generate_series(0,1000) x;
+create index patest0i on patest0(id);
+create index patest1i on patest1(id);
+create index patest2i on patest2(id);
+analyze patest0;
+analyze patest1;
+analyze patest2;
+explain (costs off)
+select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
+                        QUERY PLAN                        
+----------------------------------------------------------
+ Nested Loop
+   ->  Limit
+         ->  Seq Scan on int4_tbl
+   ->  Append
+         ->  Index Scan using patest0i on patest0
+               Index Cond: (id = int4_tbl.f1)
+         ->  Index Scan using patest1i on patest1 patest0
+               Index Cond: (id = int4_tbl.f1)
+         ->  Index Scan using patest2i on patest2 patest0
+               Index Cond: (id = int4_tbl.f1)
+(10 rows)
+
+select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
+ id | x | f1 
+----+---+----
+  0 | 0 |  0
+  0 | 0 |  0
+  0 | 0 |  0
+(3 rows)
+
+drop index patest2i;
+explain (costs off)
+select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
+                        QUERY PLAN                        
+----------------------------------------------------------
+ Nested Loop
+   ->  Limit
+         ->  Seq Scan on int4_tbl
+   ->  Append
+         ->  Index Scan using patest0i on patest0
+               Index Cond: (id = int4_tbl.f1)
+         ->  Index Scan using patest1i on patest1 patest0
+               Index Cond: (id = int4_tbl.f1)
+         ->  Seq Scan on patest2 patest0
+               Filter: (int4_tbl.f1 = id)
+(10 rows)
+
+select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
+ id | x | f1 
+----+---+----
+  0 | 0 |  0
+  0 | 0 |  0
+  0 | 0 |  0
+(3 rows)
+
+drop table patest0 cascade;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to table patest1
+drop cascades to table patest2
+--
 -- Test merge-append plans for inheritance trees
 --
 create table matest0 (id serial primary key, name text);