> On Aug 11, 2021, at 7:51 AM, Mark Dilger <mark.dilger@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,
withsuccessive 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
fromthe last post. After the patch, 72294 estimates got better and 30654 got worse. The biggest losers from this data
setare:
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
thepatch. That's just the nature of estimating. But it does bother me a bit that some types of estimates consistently
getworse. 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
reasonsome users will have those data distributions and queries, and could easily notice.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company