Re: Use extended statistics to estimate (Var op Var) clauses - Mailing list pgsql-hackers

On 8/11/21 4:51 PM, Mark Dilger wrote:
> 
> 
>> On Aug 11, 2021, at 5:08 AM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>>
>> This feels like rather an artificial example though. Is there any real
>> use for this sort of clause?
> 
> The test generated random combinations of clauses and then checked if any had consistently worse performance.  These
cameup.  I don't know that they represent anything real.
 
> 
> What was not random in the tests was the data in the tables.  I've gotten curious if these types of clauses (with
columnscompared against themselves) would still be bad for random rather than orderly data sets.  I'll go check....
 
> 
>     testing....
> 
> Wow.  Randomizing the data makes the problems even more extreme.  It seems my original test set was actually playing
tothis patch's strengths, not its weaknesses.  I've changed the columns to double precision and filled the columns with
random()data, where column1 gets random()^1, column2 gets random()^2, etc.  So on average the larger numbered columns
willbe smaller, and the mcv list will be irrelevant, since values should not tend to repeat.
 
> 
> Over all queries, 47791 have better estimates after the patch, but 34802 had worse estimates after the patch (with
theremaining 17407 queries having roughly equal quality).
 
> 
> The worst estimates are still ones that have a column compared to itself:
> 
> better:0, worse:33:  A <= B or A <= A or A <= A
> better:0, worse:33:  A <= B or A = A or not A <> A
> better:0, worse:33:  A <= B or A >= A or not A <> A
> better:0, worse:33:  A <> B or A <= A
> better:0, worse:33:  A <> B or A <= A or A <> A
> better:0, worse:33:  A <> B or A <= A or A >= A
> better:0, worse:33:  A <> B or A <= A or not A = A
> better:0, worse:33:  A <> B or A > A or not A < A
> better:0, worse:33:  A <> B or A >= A
> better:0, worse:33:  A <> B or A >= A and A <= A
> better:0, worse:33:  A = B or not A > A or not A > A
> better:0, worse:33:  A >= B or not A <> A or A = A
> better:0, worse:39:  B <= A or B <= B or B <= B
> better:0, worse:39:  B <= A or B = B or not B <> B
> better:0, worse:39:  B <= A or B >= B or not B <> B
> better:0, worse:39:  B <> A or B <= B
> better:0, worse:39:  B <> A or B <= B or B <> B
> better:0, worse:39:  B <> A or B <= B or B >= B
> better:0, worse:39:  B <> A or B <= B or not B = B
> better:0, worse:39:  B <> A or B > B or not B < B
> better:0, worse:39:  B <> A or B >= B
> better:0, worse:39:  B <> A or B >= B and B <= B
> better:0, worse:39:  B = A or not B > B or not B > B
> better:0, worse:39:  B >= A or not B <> B or B = B
> 

The other interesting thing all those clauses have in common is that 
they're OR clauses. And we handle that a bit differently. But I think 
the "strange" clauses with the same Var on both sides is the main issue, 
and not detecting them as "simple" clauses should fix that.

> But there are plenty that got worse without that, such as the following examples:
> 
> better:25, worse:39:  A < B and A < B or B > A
> better:10, worse:48:  A < B and A < C
> better:10, worse:54:  A < B and A < C or C > A
> 
> I'll go test random data designed to have mcv lists of significance....
> 

Hard to say without having a look at the data set, but there'll always 
be cases where the extended stats perform a bit worse, due to (a) luck 
and (b) the stats covering only small fraction of the table.

But of course, it's worth investigating the suspicious cases.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company