Date: Sun, 17 Jan 2021 22:53:31 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>, pgsql-hackers@postgresql.org
Subject: Re: PoC/WIP: Extended statistics on expressions
Message-ID: <20210118045331.GF8560@telsasoft.com>
References: 
 <CAEZATCWGYooC_Tzt8sQo7760ZEsguj0JGoMLhkANrDwJ9CY20Q@mail.gmail.com>
 <2567ae7e-e1dd-48b5-054d-ed6ff81c0335@enterprisedb.com>
 <CAEZATCW_7nfeHmLtV6TzSfmFG+i=io_Qo0NL8BiBnmEqq3CQnQ@mail.gmail.com>
 <CAEZATCUfMM43sv+RFDqyczQpYMP7SZLd4EexFEEUX4buWRNnrw@mail.gmail.com>
 <afcd65d7-7701-a469-b064-9c62d63ef437@enterprisedb.com>
 <20210108023537.GA19743@telsasoft.com>
 <d7c93270-20a0-93cb-f15e-46d7b51b35c1@enterprisedb.com>
 <5305f9cf-3927-06c4-cd5b-e46cbc58bc7f@enterprisedb.com>
 <20210116232208.GB8560@telsasoft.com>
 <2f2a3b3b-93e4-723f-144e-bc21f10d6b61@enterprisedb.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <2f2a3b3b-93e4-723f-144e-bc21f10d6b61@enterprisedb.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On Sun, Jan 17, 2021 at 01:23:39AM +0100, Tomas Vondra wrote:
> > CREATE TABLE t AS SELECT generate_series(1,9) AS i;
> > CREATE STATISTICS s ON (i+1) ,(i+1+0) FROM t;
> > ANALYZE t;
> > SELECT i+1 FROM t GROUP BY 1;
> > ERROR:  corrupt MVNDistinct entry
> 
> Thanks. There was a thinko in estimate_multivariate_ndistinct, resulting in
> mismatching the ndistinct coefficient items. The attached patch fixes that,
> but I've realized the way we pick the "best" statistics may need some
> improvements (I added an XXX comment about that).

That maybe indicates a deficiency in testing and code coverage.

| postgres=# CREATE TABLE t(i int);
| postgres=# CREATE STATISTICS s2 ON (i+1) ,(i+1+0) FROM t;
| postgres=# \d t
|                  Table "public.t"
|  Column |  Type   | Collation | Nullable | Default 
| --------+---------+-----------+----------+---------
|  i      | integer |           |          | 
| Indexes:
|     "t_i_idx" btree (i)
| Statistics objects:
|     "public"."s2" (ndistinct, dependencies, mcv) ON  FROM t

on ... what ?

-- 
Justin