Subject: Re: [HACKERS] PATCH: multivariate histograms and MCV lists
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: Dean Rasheed <dean.a.rasheed@gmail.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>,
 Pg Hackers <pgsql-hackers@postgresql.org>
References: <b114496c-ee55-11f0-b17a-b00ec22677bc@2ndquadrant.com>
 <20190206012140.GA3626@alvherre.pgsql>
 <CAKJS1f_gA+odYxRD45LHrKhFKF00CGBKWELQxAGF=L6=6NqWVw@mail.gmail.com>
 <53386926-08a6-76f7-6a8f-f36e4ffb1b9d@2ndquadrant.com>
 <60a3741a-dcd5-4a30-90a2-c427b94524fc@2ndquadrant.com>
 <CAEZATCWAT3hEvWv2JoMfioroy41eKb7roCaM3D0b-BDAoKTbFw@mail.gmail.com>
 <CAEZATCVG+xCQc8NGx_LCY8xr-RcwaxQLfibkMnTT5A60DVturg@mail.gmail.com>
 <5c865c03-9963-c62a-ef0c-c42f3c0e855f@2ndquadrant.com>
 <CAKJS1f_2yGfR2Q3vEYwQ2aoHhs_QDYT5mq_YUyaobnEHCCJmSg@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <d9bd905d-8b67-8eb7-9b38-11ba8345206b@2ndquadrant.com>
Date: Mon, 11 Mar 2019 03:59:33 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.5.1
MIME-Version: 1.0
In-Reply-To: 
 <CAKJS1f_2yGfR2Q3vEYwQ2aoHhs_QDYT5mq_YUyaobnEHCCJmSg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk


On 3/10/19 11:27 PM, David Rowley wrote:
> On Mon, 11 Mar 2019 at 06:36, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>>
>> On 3/9/19 7:33 PM, Dean Rasheed wrote:
>>> I wonder if it's possible to write smaller, more targeted tests.
>>> Currently "stats_ext" is by far the slowest test in its group, and I'm
>>> not sure that some of those tests add much. It ought to be possible to
>>> write a function that calls EXPLAIN and returns a query's row
>>> estimate, and then you could write tests to confirm the effect of the
>>> new stats by verifying the row estimates change as expected.
>>
>> Sure, if we can write more targeted tests, that would be good. But it's
>> not quite clear to me how wrapping EXPLAIN in a function makes those
>> tests any faster?
> 
> I've not looked at the tests in question, but if they're executing an
> inferior plan is used when no extended stats exists, then maybe that's
> why they're slow.
> 

I don't think the tests are executing any queries - the tests merely
generate execution plans, without executing them.

> I think Dean might mean to create a function similar to
> explain_parallel_append() in partition_prune.sql then write tests that
> check the row estimate with EXPLAIN (COSTS ON) but strip out the other
> costing stuff instead of validating that the poor plan was chosen.
> 

I'm not opposed to doing that, of course. I'm just not sure it's a way
to make the tests faster. Will investigate.

>> On 3/10/19 2:09 PM, Dean Rasheed wrote:
>>> 12). bms_member_index() should surely be in bitmapset.c. It could be
>>> more efficient by just traversing the bitmap words and making use of
>>> bmw_popcount(). Also, its second argument should be of type 'int' for
>>> consistency with other bms_* functions.
>>
>> Yes, moving to bitmapset.c definitely makes sense. I don't see how it
>> could use bms_popcount() though.
> 
> I think it could be done by first checking if the parameter is a
> member of the set, and then if so, count all the bits that come on and
> before that member. You can use bmw_popcount() for whole words before
> the specific member's word then just bitwise-and a bit mask of a
> bitmapword that has all bits set for all bits on and before your
> parameter's BITNUM(), and add the bmw_popcount of the final word
> bitwise-anding the mask. bms_add_range() has some masking code you
> could copy.
> 

Ah, right - that would work.


cheers

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services