Re: Costing elided SubqueryScans more nearly correctly

On Thu, May 5, 2022 at 7:03 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote:
> > I instrumented the code in setrefs.c, and found that during the
> > core regression tests this patch estimates correctly in 2103
> > places while guessing wrongly in 54, so that seems like a pretty
> > good step forward.
>
> On second thought, that's not a terribly helpful summary. Breaking
> things down to the next level, there were
>
> 1088 places where we correctly guessed a subquery isn't trivial
> (so no change from current behavior, which is correct)
>
> 1015 places where we correctly guessed a subquery is trivial
> (hence, improving the cost estimate from before)
>
> 40 places where we incorrectly guessed a subquery isn't trivial
> (so no change from current behavior, although that's wrong)
>
> 14 places where we incorrectly guessed a subquery is trivial
> (hence, incorrectly charging zero for the SubqueryScan)
>
> 1015 improvements to 14 disimprovements isn't a bad score. I'm
> a bit surprised there are that many removable SubqueryScans TBH;
> maybe that's an artifact of all the "SELECT *" queries.
>

The patch looks sane to me. 1015 vs 14 is a good win.

Thanks
Richard