Re: Merging statistics from children instead of re-sampling everything - Mailing list pgsql-hackers

On 2/14/22 20:16, Tomas Vondra wrote:
> 
> 
> On 2/14/22 11:22, Andrey V. Lepikhov wrote:
>> 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.
>>
> 
> I doubt keeping the samples just in memory is a good solution. Firstly, 
> there's the question of memory consumption. Imagine a large partitioned 
> table with 1-10k partitions. If we keep a "regular" sample (30k rows) 
> per partition, that's 30M-300M rows. If each row needs 100B, that's 
> 3-30GB of data.
I tell about caching a sample only for a time that it needed in this 
ANALYZE operation. Imagine 3 levels of partitioned table. On each 
partition you should create and keep three different samples (we can do 
it in one scan). Sample for a plane table we can use immediately and 
destroy it.
Sample for the partition on second level of hierarchy: we can save a 
copy of sample for future usage (maybe, repeated analyze) to a disk. 
In-memory data used to form a reservoir, that has a limited size and can 
be destroyed immediately. At the third level we can use the same logic.
So, at one moment we only use as many samples as many levels of 
hierarchy we have. IMO, it isn't large number.

 > the trouble is partitions may be detached, data may be deleted from
 > some partitions, etc.
Because statistics hasn't strong relation with data, we can use two 
strategies: In the case of explicit 'ANALYZE <table>' we can recalculate 
all samples for all partitions, but in autovacuum case or implicit 
analysis we can use not-so-old versions of samples and samples of 
detached (but not destroyed) partitions in optimistic assumption that it 
doesn't change statistic drastically.

> So IMHO the samples need to be serialized, in some way.
Agreed

> Well, a separate catalog is one of the options. But I don't see how that 
> deals with large samples, etc.
I think, we can design fall back to previous approach in the case of 
very large tuples, like a switch from HashJoin to NestedLoop if we 
estimate, that we haven't enough memory.

-- 
regards,
Andrey Lepikhov
Postgres Professional