Message-ID: <f1435fb9-e78d-bd43-ec2a-e1477db1ab32@postgrespro.ru>
Date: Mon, 14 Feb 2022 15:22:30 +0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:91.0)
 Gecko/20100101 Thunderbird/91.6.0
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Subject: Re: Merging statistics from children instead of re-sampling
 everything
To: Tomas Vondra <tomas.vondra@enterprisedb.com>,
 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>
 <901c13d7-b37f-3a2d-062a-3696d58d030c@enterprisedb.com>
Content-Language: en-US
Organization: Postgres Professional
In-Reply-To: <901c13d7-b37f-3a2d-062a-3696d58d030c@enterprisedb.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/f1435fb9-e78d-bd43-ec2a-e1477db1ab32%40postgrespro.ru>
Precedence: bulk

On 2/11/22 20:12, Tomas Vondra wrote:
> 
> 
> 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.
> 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 suppose to make a set of samples in one scan: one sample for plane 
table, another - for a parent and so on, according to the inheritance 
tree. And cache these samples in memory. We can calculate all parameters 
of reservoir method to do it.

> sample might be used for estimation of clauses directly.
You mean, to use them in difficult cases, such of estimation of grouping 
over APPEND ?
> 
> 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)? 
This feature looks like meta-info over a database. It can be stored in 
separate relation. It is not obvious that we need to use it for each 
relation, for example, with large samples. I think, it can be controlled 
by a table parameter.

-- 
regards,
Andrey Lepikhov
Postgres Professional