postgres.git / summary / log / commit / refs

commit    ca5e93f769e1bf5e90d080cfcc8c0368ef649a7c
Author:   Tom Lane <tgl@sss.pgh.pa.us>
Date:     Fri Jul 03 23:01:21 2020 +0000
First released in: 14.0, 9.5.23, 9.6.19, 10.14, 11.9, 12.4, 13.0

    Clamp total-tuples estimates for foreign tables to ensure planner sanity.
    
    After running GetForeignRelSize for a foreign table, adjust rel->tuples
    to be at least as large as rel->rows.  This prevents bizarre behavior
    in estimate_num_groups() and perhaps other places, especially in the
    scenario where rel->tuples is zero because pg_class.reltuples is
    (suggesting that ANALYZE has never been run for the table).  As things
    stood, we'd end up estimating one group out of any GROUP BY on such a
    table, whereas the default group-count estimate is more likely to result
    in a sane plan.
    
    Also, clarify in the documentation that GetForeignRelSize has the option
    to override the rel->tuples value if it has a better idea of what to use
    than what is in pg_class.reltuples.
    
    Per report from Jeff Janes.  Back-patch to all supported branches.
    
    Patch by me; thanks to Etsuro Fujita for review
    
    Discussion: https://postgr.es/m/CAMkU=1xNo9cnan+Npxgz0eK7394xmjmKg-QEm8wYG9P5-CcaqQ@mail.gmail.com


 doc/src/sgml/fdwhandler.sgml          | 8 ++++++++
 src/backend/optimizer/path/allpaths.c | 3 +++
 2 files changed, 11 insertions(+)

diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
index 6587678af2b..1e997c218b9 100644
--- a/doc/src/sgml/fdwhandler.sgml
+++ b/doc/src/sgml/fdwhandler.sgml
@@ -123,6 +123,14 @@ GetForeignRelSize(PlannerInfo *root,
      should be replaced if at all possible.  The function may also choose to
      update <literal>baserel-&gt;width</literal> if it can compute a better estimate
      of the average result row width.
+     (The initial value is based on column data types and on column
+     average-width values measured by the last <command>ANALYZE</command>.)
+     Also, this function may update <literal>baserel-&gt;tuples</literal> if
+     it can compute a better estimate of the foreign table's total row count.
+     (The initial value is
+     from <structname>pg_class</structname>.<structfield>reltuples</structfield>
+     which represents the total row count seen by the
+     last <command>ANALYZE</command>.)
     </para>
 
     <para>
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index d984da25d77..63761d55938 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -911,6 +911,9 @@ set_foreign_size(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
 
 	/* ... but do not let it set the rows estimate to zero */
 	rel->rows = clamp_row_est(rel->rows);
+
+	/* also, make sure rel->tuples is not insane relative to rel->rows */
+	rel->tuples = Max(rel->tuples, rel->rows);
 }
 
 /*


[parent: f7b5988cc0d8]