Message-ID: <901c13d7-b37f-3a2d-062a-3696d58d030c@enterprisedb.com>
Date: Fri, 11 Feb 2022 16:12:16 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.5.0
Subject: Re: Merging statistics from children instead of re-sampling
 everything
Content-Language: en-US
To: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: d.belyalov@postgrespro.ru
References: <c3162511-0e80-039e-765c-1d02d0fd0bab@enterprisedb.com>
 <078c36f0-fc9c-b2d9-2bae-8eebafcefe93@enterprisedb.com>
 <92ce5a13-f97f-b4e8-c0f7-01c53d917f4f@postgrespro.ru>
 <d487b29a-2980-70dc-0091-42a2c8ee1136@enterprisedb.com>
 <82fcba0a-7c50-c714-2a6b-f2677affe65d@postgrespro.ru>
 <d35c52a6-3242-e559-4ba6-c0e6de3fa1b1@enterprisedb.com>
 <949f204b-44e2-4e41-67ff-f93643828f4a@enterprisedb.com>
 <bdb0bea2-a0da-1f1d-5c92-96ff90c198eb@postgrespro.ru>
 <4e86ae74-4e2c-b40f-4405-035d2f818e5d@enterprisedb.com>
 <90e400a2-9afa-15c6-8c3c-bac9f68cb9f3@postgrespro.ru>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <90e400a2-9afa-15c6-8c3c-bac9f68cb9f3@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/901c13d7-b37f-3a2d-062a-3696d58d030c%40enterprisedb.com>
Precedence: bulk


On 2/11/22 05:29, Andrey V. Lepikhov wrote:
> On 2/11/22 03:37, Tomas Vondra wrote:
>> That being said, this thread was not really about foreign partitions,
>> but about re-analyzing inheritance trees in general. And sampling
>> foreign partitions doesn't really solve that - we'll still do the
>> sampling over and over.
> IMO, to solve the problem we should do two things:
> 1. Avoid repeatable partition scans in the case inheritance tree.
> 2. Avoid to re-analyze everything in the case of active changes in small 
> subset of partitions.
> 
> For (1) i can imagine a solution like multiplexing: on the stage of 
> defining which relations to scan, group them and prepare parameters of 
> scanning to make multiple samples in one shot.
 >> It looks like we need a separate logic for analysis of partitioned
> tables - we should form and cache samples on each partition before an 
> analysis.
> It requires a prototype to understand complexity of such solution and 
> can be done separately from (2).
> 

I'm not sure I understand what you mean by multiplexing. The term 
usually means "sending multiple signals at once" but I'm not sure how 
that applies to this issue. Can you elaborate?

I assume you mean something like collecting a sample for a partition 
once, and then keeping and reusing the sample for future ANALYZE runs, 
until invalidated in some sense.

Yeah, I agree that'd be useful - and not just for partitions, actually. 
I've been pondering something like that for regular tables, because the 
sample might be used for estimation of clauses directly.

But it requires storing the sample somewhere, and I haven't found a good 
and simple way to do that. We could serialize that into bytea, or we 
could create a new fork, or something, but what should that do with 
oversized attributes (how would TOAST work for a fork) and/or large 
samples (which might not fit into 1GB bytea)?


> Task (2) is more difficult to solve. Here we can store samples from each 
> partition in values[] field of pg_statistic or in specific table which 
> stores a 'most probable values' snapshot of each table.

I think storing samples in pg_statistic is problematic, because values[] 
is subject to 1GB limit etc. Not an issue for small MCV list of a single 
attribute, certainly an issue for larger samples. Even if the data fit, 
the size of pg_statistic would explode.

> Most difficult problem here, as you mentioned, is ndistinct value. Is it 
> possible to store not exactly calculated value of ndistinct, but an 
> 'expected value', based on analysis of samples and histograms on 
> partitions? Such value can solve also a problem of estimation of a SETOP 
> result grouping (joining of them, etc), where we have statistics only on 
> sources of the union.
> 

I think ndistinct is problem only when merging final estimates. But if 
we're able to calculate and store some intermediate results, we can 
easily use HLL and merge that.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company