Re: Using multiple extended statistics for estimates

On 11/6/19 11:58 AM, Tomas Vondra wrote:
> On Wed, Nov 06, 2019 at 08:54:40PM +0100, Tomas Vondra wrote:
>> On Mon, Oct 28, 2019 at 04:20:48PM +0100, Tomas Vondra wrote:
>>> Hi,
>>>
>>> PostgreSQL 10 introduced extended statistics, allowing us to consider
>>> correlation between columns to improve estimates, and PostgreSQL 12
>>> added support for MCV statistics. But we still had the limitation that
>>> we only allowed using a single extended statistics per relation, i.e.
>>> given a table with two extended stats
>>>
>>>  CREATE TABLE t (a int, b int, c int, d int);
>>>  CREATE STATISTICS s1 (mcv) ON a, b FROM t;
>>>  CREATE STATISTICS s2 (mcv) ON c, d FROM t;
>>>
>>> and a query
>>>
>>>  SELECT * FROM t WHERE a = 1 AND b = 1 AND c = 1 AND d = 1;
>>>
>>> we only ever used one of the statistics (and we considered them in a not
>>> particularly well determined order).
>>>
>>> This patch addresses this by using as many extended stats as possible,
>>> by adding a loop to statext_mcv_clauselist_selectivity(). In each step
>>> we pick the "best" applicable statistics (in the sense of covering the
>>> most attributes) and factor it into the oveall estimate.

Tomas,

Your patch compiles and passes the regression tests for me on debian
linux under master.

Since your patch does not include modified regression tests, I wrote a
test that I expected to improve under this new code, but running it both
before and after applying your patch, there is no change. Please find
the modified test attached. Am I wrong to expect some change in this
test's output? If so, can you provide a test example that works
differently under your patch?

Thanks!

--
Mark Dilger