pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: PoC/WIP: Extended statistics on expressions
Date: Wed, 17 Mar 2021 20:07:22 +0100
Message-ID: <f4dac079-6bc4-ccc0-0fd8-3e2e2da28d92@enterprisedb.com> (raw)
In-Reply-To: <CAEZATCWF66BWJq-OwLuP5LGK6W9LDYxCQwLxqB36qmq3b1Ch8Q@mail.gmail.com>
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>



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





view thread (107+ messages)  latest in thread

Message-ID: <f4dac079-6bc4-ccc0-0fd8-3e2e2da28d92@enterprisedb.com>
Permalink:  ../f4dac079-6bc4-ccc0-0fd8-3e2e2da28d92@enterprisedb.com/
Also on:    postgresql.org/message-id/f4dac079-6bc4-ccc0-0fd8-3e2e2da28d92@enterprisedb.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: tomas.vondra@enterprisedb.com, dean.a.rasheed@gmail.com, pryzby@telsasoft.com
  Subject: Re: PoC/WIP: Extended statistics on expressions
  In-Reply-To: <f4dac079-6bc4-ccc0-0fd8-3e2e2da28d92@enterprisedb.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox