Message-ID: <90e400a2-9afa-15c6-8c3c-bac9f68cb9f3@postgrespro.ru>
Date: Fri, 11 Feb 2022 09:29:38 +0500
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: Tomas Vondra <tomas.vondra@enterprisedb.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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>
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
Cc: d.belyalov@postgrespro.ru
In-Reply-To: <4e86ae74-4e2c-b40f-4405-035d2f818e5d@enterprisedb.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/90e400a2-9afa-15c6-8c3c-bac9f68cb9f3%40postgrespro.ru>
Precedence: bulk

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).

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.
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.

-- 
regards,
Andrey Lepikhov
Postgres Professional