> On Aug 11, 2021, at 7:51 AM, Mark Dilger <mark.dil...@enterprisedb.com> wrote:
>
> I'll go test random data designed to have mcv lists of significance....
Done. The data for column_i is set to floor(random()^i*20). column_1
therefore is evenly distributed between 0..19, with successive columns weighted
more towards smaller values.
This still gives (marginally) worse results than the original test I posted,
but better than the completely random data from the last post. After the
patch, 72294 estimates got better and 30654 got worse. The biggest losers from
this data set are:
better:0, worse:31: A >= B or A = A or not A = A
better:0, worse:31: A >= B or A = A
better:0, worse:31: A >= B or not A <> A
better:0, worse:31: A >= A or A = B or not B = A
better:0, worse:31: A >= B and not A < A or A = A
better:0, worse:31: A = A or not A > B or B <> A
better:0, worse:31: A >= B or not A <> A or not A >= A
better:0, worse:32: B < A and B > C and not C < B <----
better:1, worse:65: A <> C and A <= B <----
better:0, worse:33: B <> A or B >= B
better:0, worse:33: B <> A or B <= B
better:0, worse:33: B <= A or B = B or not B > B
better:0, worse:33: B <> A or not B >= B or not B < B
better:0, worse:33: B = A or not B > B or B = B
better:0, worse:44: A = B or not A > A or A = A
better:0, worse:44: A <> B or A <= A
better:0, worse:44: A <> B or not A >= A or not A < A
better:0, worse:44: A <= B or A = A or not A > A
better:0, worse:44: A <> B or A >= A
Of which, a few do not contain columns compared against themselves, marked with
<---- above.
I don't really know what to make of these results. It doesn't bother me that
any particular estimate gets worse after the patch. That's just the nature of
estimating. But it does bother me a bit that some types of estimates
consistently get worse. We should either show that my analysis is wrong about
that, or find a way to address it to avoid performance regressions. If I'm
right that there are whole classes of estimates that are made consistently
worse, then it stands to reason some users will have those data distributions
and queries, and could easily notice.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
