Re: Using multiple extended statistics for estimates

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Using multiple extended statistics for estimates
Date: 2019-11-06 19:58:49
Message-ID: 20191106195849.odhhc66xd23hw6hf@development
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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:
>>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.
>>All this happens where we'd originally consider applying a single MCV
>>list, i.e. before even considering the functional dependencies, so
>>roughly like this:
>> while ()
>> {
>> ... apply another MCV list ...
>> }
>> ... apply functional dependencies ...
>>I've both in the loop, but I think that'd be wrong - the MCV list is
>>expected to contain more information about individual values (compared
>>to functional deps, which are column-level).
>Here is a slightly polished v2 of the patch, the main difference being
>that computing clause_attnums was moved to a separate function.

This time with the attachment ;-)

Tomas Vondra
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
use-multiple-extended-stats-v2.patch text/plain 7.8 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Juan José Santamaría Flecha 2019-11-06 20:41:56 Re: TAP tests aren't using the magic words for Windows file access
Previous Message Tomas Vondra 2019-11-06 19:54:40 Re: Using multiple extended statistics for estimates