Subject: Re: PoC/WIP: Extended statistics on expressions
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
References: <20210108023537.GA19743@telsasoft.com>
 <20210118045331.GF8560@telsasoft.com>
 <CAEZATCVnQccV_Z7AuvbvquaOYxUj+cVFk48pPGASYHFjXWgW5g@mail.gmail.com>
 <3f20da4d-89dc-0912-98d2-b6f0ef06c87f@enterprisedb.com>
 <CAEZATCUuj5FPjtOU2=7Z2pTC8LEvrf79+Go7R8yQ+eE0ezfgfQ@mail.gmail.com>
 <f865af90-c19c-769e-8c65-19692e245479@enterprisedb.com>
 <20210122022958.GC27167@telsasoft.com>
 <acf27932-8159-8872-8fca-902c7a35b351@enterprisedb.com>
 <CAEZATCXvarTUJthsnoSMzhQwivUqsxGcvSm_5h1L-yNapVyo-g@mail.gmail.com>
 <2147e075-fef6-b1d5-e5f5-7f92ffefe827@enterprisedb.com>
 <d571f2de-6246-4f6e-ca1d-32cc53a2016a@enterprisedb.com>
 <f3820f57-dd45-8ea3-0c46-86629b0c0d41@enterprisedb.com>
 <CAEZATCUo1xA4d5e=kXw5E0z7xKEi1oL_aLx=wBGT1A4vdc_gYw@mail.gmail.com>
 <1913c1fe-13da-7250-adf4-befeda397536@enterprisedb.com>
 <CAEZATCUooH7uPsPkMJEyf-KY_3Om7TTVo7Fo31JiXXgGCb=97g@mail.gmail.com>
 <f0f7e00c-7ccb-9412-0147-a06a60777f06@enterprisedb.com>
 <CAEZATCWF66BWJq-OwLuP5LGK6W9LDYxCQwLxqB36qmq3b1Ch8Q@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <f4dac079-6bc4-ccc0-0fd8-3e2e2da28d92@enterprisedb.com>
Date: Wed, 17 Mar 2021 20:07:22 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CAEZATCWF66BWJq-OwLuP5LGK6W9LDYxCQwLxqB36qmq3b1Ch8Q@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/f4dac079-6bc4-ccc0-0fd8-3e2e2da28d92%40enterprisedb.com>
Precedence: bulk


On 3/17/21 7:54 PM, Dean Rasheed wrote:
> On Wed, 17 Mar 2021 at 17:26, Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>>
>> My concern is that the current behavior (where we prefer expression
>> stats over multi-column stats to some extent) works fine as long as the
>> parts are independent, but once there's dependency it's probably more
>> likely to produce underestimates. I think underestimates for grouping
>> estimates were a risk in the past, so let's not make that worse.
>>
> 
> I'm not sure the current behaviour really is preferring expression
> stats over multi-column stats. In this example, where we're grouping
> by (a+b), (c+d) and have stats on [(a+b),c] and (c+d), neither of
> those multi-column stats actually match more than one
> column/expression. If anything, I'd go the other way and say that it
> was wrong to use the [(a+b),c] stats in the first case, where they
> were the only stats available, since those stats aren't really
> applicable to (c+d), which probably ought to be treated as
> independent. IOW, it might have been better to estimate the first case
> as
> 
>      ndistinct((a+b)) * ndistinct(c) * ndistinct(d)
> 
> and the second case as
> 
>      ndistinct((a+b)) * ndistinct((c+d))
> 

OK. I might be confused, but isn't that what the algorithm currently
does? Or am I just confused about what the first/second case refers to?


regards

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