On Sun, Oct 11, 2020 at 1:39 PM Thomas Munro <thomas.munro@gmail.com> wrote:
>
> Yeah, I think this is trying to fix the problem too late. Instead, we
> should fix the incorrect row estimates so we don't have to fudge it
> later like that. For example, this should be estimating rows=0:
>
> postgres=# explain analyze insert into s select * from t t1 join t t2 using (i);
> ...
> Insert on s (cost=30839.08..70744.45 rows=1000226 width=4) (actual
> time=2940.560..2940.562 rows=0 loops=1)
>
> I think that should be done with something like this:
>
> --- a/src/backend/optimizer/util/pathnode.c
> +++ b/src/backend/optimizer/util/pathnode.c
> @@ -3583,16 +3583,11 @@ create_modifytable_path(PlannerInfo *root,
> RelOptInfo *rel,
> if (lc == list_head(subpaths)) /* first node? */
> pathnode->path.startup_cost = subpath->startup_cost;
> pathnode->path.total_cost += subpath->total_cost;
> - pathnode->path.rows += subpath->rows;
> + if (returningLists != NIL)
> + pathnode->path.rows += subpath->rows;
> total_size += subpath->pathtarget->width * subpath->rows;
> }
>
> - /*
> - * Set width to the average width of the subpath outputs. XXX this is
> - * totally wrong: we should report zero if no RETURNING, else an average
> - * of the RETURNING tlist widths. But it's what happened historically,
> - * and improving it is a task for another day.
> - */
> if (pathnode->path.rows > 0)
> total_size /= pathnode->path.rows;
> pathnode->path.pathtarget->width = rint(total_size);
Agree, thanks (bug in existing Postgres code, right?)
Regards,
Greg Nancarrow
Fujitsu Australia