On 8/11/21 5:17 PM, Mark Dilger wrote:
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.
I'm not quite sure that's really a problem. Extended statistics are
meant for correlated columns, and it's mostly expected the estimates may
be a bit worse for random / independent data. The idea is mostly that
statistics will be created only for correlated columns, in which case it
should improve the estimates. I'd be way more concerned if you observed
consistently worse estimates on such data set.
Of course, there may be errors - the incorrect handling of (A op A) is
an example of such issue, probably.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
