From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Andres Freund <andres@anarazel.de>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Mark Dilger <hornschnorter@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] PATCH: multivariate histograms and MCV lists
Date: Sun, 17 Mar 2019 14:19:25 +0100
Message-ID: <2f373a2e-b014-64b2-6e20-5c6276ed2c2e@2ndquadrant.com> (raw)
In-Reply-To: <CAEZATCWK9j=tfzysBAgpU6KDZE8JyU8G_09r_GDUwhz42iqn6Q@mail.gmail.com>
References: <19f76496-dcf3-ccea-dd82-26fbed57b8f5@2ndquadrant.com>
<20190313.131951.178195776.horiguchi.kyotaro@lab.ntt.co.jp>
<CAKJS1f_6qDQj9m2H0jF4bRkZVLpfc7O9E+MxdXrq0wgv0z1NrQ@mail.gmail.com>
<20190314.205623.235434357.horiguchi.kyotaro@lab.ntt.co.jp>
<c2406825-6ebe-26e6-91b3-ba94026546a6@2ndquadrant.com>
<CAEZATCVazGRDjbZpRF6r-Asiv_-U8vcT-VA0oSZribhhmDUQHQ@mail.gmail.com>
<37ce47c5-e0cf-c6d3-6923-6b8cfd1cc6b1@2ndquadrant.com>
<CAEZATCWK9j=tfzysBAgpU6KDZE8JyU8G_09r_GDUwhz42iqn6Q@mail.gmail.com>
On 3/17/19 1:14 PM, Dean Rasheed wrote:
> On Sat, 16 Mar 2019 at 23:44, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>>>
>>> 16). This regression test fails for me:
>>>
>>> @@ -654,11 +654,11 @@
>>> -- check change of unrelated column type does not reset the MCV statistics
>>> ALTER TABLE mcv_lists ALTER COLUMN d TYPE VARCHAR(64);
>>> SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a =
>>> 1 AND b = ''1''');
>>> estimated | actual
>>> -----------+--------
>>> - 50 | 50
>>> + 11 | 50
>>> (1 row)
>>>
>>> Maybe that's platform-dependent, given what you said about
>>> reltuples/relpages being reset. An easy workaround for this would be
>>> to modify this test (and perhaps the one that follows) to just query
>>> pg_statistic_ext to see if the MCV statistics have been reset.
>>>
>>
>> Ah, sorry for not explaining this bit - the failure is expected, due to
>> the reset of relpages/reltuples I mentioned. We do keep the extended
>> stats, but the relsize estimate changes a bit. It surprised me a bit,
>> and this test made the behavior apparent. The last patchset included a
>> piece that changes that - if we decide not to change this, I think we
>> can simply accept the actual output.
>>
>
> I don't think changing the way reltuples is reset ought to be within
> the scope of this patch. There might be good reasons for it being the
> way it is. Perhaps open a discussion on a separate thread?
>
Agreed, will do.
> As far as this test goes, how about just doing this:
>
> -- check change of unrelated column type does not reset the MCV statistics
> ALTER TABLE mcv_lists ALTER COLUMN d TYPE VARCHAR(64);
> SELECT stxmcv IS NOT NULL AS has_mcv
> FROM pg_statistic_ext WHERE stxrelid = 'mcv_lists'::regclass;
>
> -- check change of column type resets the MCV statistics
> ALTER TABLE mcv_lists ALTER COLUMN c TYPE numeric;
> SELECT stxmcv IS NOT NULL AS has_mcv
> FROM pg_statistic_ext WHERE stxrelid = 'mcv_lists'::regclass;
>
OK, that's probably the best thing we can do.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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@2ndquadrant.com, dean.a.rasheed@gmail.com, horiguchi.kyotaro@lab.ntt.co.jp, david.rowley@2ndquadrant.com, alvherre@2ndquadrant.com, michael@paquier.xyz, andres@anarazel.de, thomas.munro@enterprisedb.com, bruce@momjian.us, hornschnorter@gmail.com
Subject: Re: [HACKERS] PATCH: multivariate histograms and MCV lists
In-Reply-To: <2f373a2e-b014-64b2-6e20-5c6276ed2c2e@2ndquadrant.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