Re: Using multiple extended statistics for estimates

On 11/14/19 12:04 PM, Tomas Vondra wrote:
> Aha, I think I understand now - thanks for the explanation. You're right
> the comment is trying to explain why just taking the last clause for a
> given attnum is fine. I'll try to make the comment clearer.
>
> For the case with equal Const values that should be mostly obvious, i.e.
> "a=1 AND a=1 AND a=1" has the same selectivity as "a=1".
>
> The case with different Const values is harder, unfortunately. It might
> seem obvious that "a=1 AND a=2" means there are no matching rows, but
> that heavily relies on the semantics of the equality operator. And we
> can't simply compare the Const values either, I'm afraid, because there
> are cases with cross-type operators like
>
>  a = 1::int AND a = 1.0::numeric
>
> where the Consts are of different type, yet both conditions can be true.
>
> So it would be pretty tricky to do this, and the current code does not
> even try to do that.
>
> Instead, it just assumes that it's mostly fine to overestimate, because
> then at runtime we'll simply end up with 0 rows here.

I'm unsure whether that could be a performance problem at runtime.

I could imagine the planner short-circuiting additional planning when
it finds a plan with zero rows, and so we'd save planner time if we
avoid overestimating. I don't recall if the planner does anything like
that, or if there are plans to implement such logic, but it might be
good not to rule it out. Tom's suggestion elsewhere in this thread to
use code in predtest.c sounds good to me.

I don't know if you want to expand the scope of this particular patch to
include that, though.

--
Mark Dilger