Re: Merging statistics from children instead of re-sampling everything

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