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: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>, David Rowley <david.rowley@2ndquadrant.com>, 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-16T10:55:40Z
Lists: pgsql-hackers 
On Fri, 15 Mar 2019 at 00:06, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
> I've noticed an annoying thing when modifying type of column not
> included in a statistics...
>
> That is, we don't remove the statistics, but the estimate still changes.
> But that's because the ALTER TABLE also resets reltuples/relpages:
>
> That's a bit unfortunate, and it kinda makes the whole effort to not
> drop the statistics unnecessarily kinda pointless :-(
>

Well not entirely. Repeating that test with 100,000 rows, I get an
initial estimate of 9850 (actual 10,000), which then drops to 2451
after altering the column. But if you drop the dependency statistics,
the estimate drops to 241, so clearly there is some benefit in keeping
them in that case.

Besides, I thought there was no extra effort in keeping the extended
statistics in this case -- isn't it just using the column
dependencies, so in this case UpdateStatisticsForTypeChange() never
gets called anyway?

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