Re: PG choosing nested loop for set membership?

On Wed, Mar 26, 2014 at 11:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> In principle I guess we could somehow merge the stats of y and z
> when looking at a "coalesce(y, z)" expression, but I'm not sure
> how that would work exactly.

Yeah, I'm not sure there's anything to fix here, either. Just a
reminder that coalesces in joins are bad.

The only thing I could think was making an exception for the case when
all inputs to coalesce() have one row. I think that's what's happening
here; coalesce selectivity is estimated at less than one, even though
you can't get a cardinality any less than the inputs (they're already
one), so the nested loop sees an estimate that's less than the product
of its inputs. Or that's my guess anyhow.

Thanks for having a look!

--Brian