Horton

Re: [HACKERS] PATCH: multivariate histograms and MCV lists

Dean Rasheed <dean.a.rasheed@gmail.com>

View thread View on postgr.es
From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: David Rowley <david.rowley@2ndquadrant.com>, Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>, Alvaro Herrera <alvherre@2ndquadrant.com>, Michael Paquier <michael@paquier.xyz>, Andres Freund <andres@anarazel.de>, Thomas Munro <thomas.munro@enterprisedb.com>, Bruce Momjian <bruce@momjian.us>, Mark Dilger <hornschnorter@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2019-03-26T13:37:33Z
Lists: pgsql-hackers 
On Tue, 26 Mar 2019 at 11:59, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> On Mon, 25 Mar 2019 at 23:36, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
> >
> > Attached is an updated patch...
>
> I just looked through the latest set of changes and I have a couple of
> additional review comments:
>

I just spotted another issue while reading the code:

It's possible to build an MCV list with more than
STATS_MCVLIST_MAX_ITEMS = 8192 items, which then causes an error when
the code tries to read it back in:

create temp table foo(a int, b int);
insert into foo select x,x from generate_series(1,10000) g(x);
insert into foo select x,x from generate_series(1,10000) g(x);
alter table foo alter column a set statistics 10000;
alter table foo alter column b set statistics 10000;
create statistics s (mcv) on a,b from foo;
analyse foo;
select * from foo where a=1 and b=1;

ERROR:  invalid length (10000) item array in MCVList

So this needs to be checked when building the MCV list.

In fact, the stats targets for table columns can be as large as 10000
(a hard-coded constant in tablecmds.c, which is pretty ugly, but
that's a different matter), so I think STATS_MCVLIST_MAX_ITEMS
probably ought to match that.

There are also a couple of comments that refer to the 8k limit, which
would need updating, if you change it.

Regards,
Dean

Commits

  1. Convert pre-existing stats_ext tests to new style

  2. Add support for multivariate MCV lists

  3. Improve ANALYZE's strategy for finding MCVs.

  4. Clone extended stats in CREATE TABLE (LIKE INCLUDING ALL)

  5. Try again to fix accumulation of parallel worker instrumentation.

  6. Adjust psql \d query to avoid use of @> operator.

  7. Message style fixes

  8. Add security checks to selectivity estimation functions