Re: Use extended statistics to estimate (Var op Var) clauses

> On Aug 11, 2021, at 10:38 AM, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
> So I'm a bit puzzled about the claim that random data make the problems more extreme. Can you explain?

Hmm... you appear to be right.

I changed the gentest.pl script to fill the tables with randomized data, but the random data is being regenerated each test run (since the calls to random() are in the gentest.sql file). Adding an explicit setseed() call in the test to make sure the data is the same before and after applying your patch eliminates the differences.

So there are three tests here. The first tests deterministic orderly data. The second tests deterministic random data without repeats and hence without meaningful mvc. The third tests deterministic random data with rounding into twenty buckets skewed towards lower numbered buckets and hence with both repeats and meaningful mvc.

The original test set:

TOTAL:
better: 77827
worse: 12317

The random test set, with setseed() calls to make it deterministic:

TOTAL:
better: 49708
worse: 19393

The random test set , with setseed() calls to make it deterministic plus rounding into buckets:

TOTAL:
better: 81764
worse: 19594

Once the data is made deterministic, the third set looks slightly better than the first, rather than slightly worse. But almost 20% of the query types still look worse after applying the patch. I'm going to dig deeper into those to see if that conclusion survives bumping up the size of the dataset. It will take quite some time to run the tests with a huge dataset, but I don't see how else to investigate this.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company