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

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.

Sure, maybe we could keep smaller per-partition samples, large enough to
get the merged sample of 30k row. But then you can also have higher
statistics target values, the rows can be larger, etc.

So a couple of GB per inheritance tree can easily happen. And this data
may not be used all that often, so keeping it in memory may be wasteful.

But maybe you have an idea how to optimize sizes per-partition samples?
In principle we need

30k * size(partition) / size(total)

for each partition, but the trouble is partitions may be detached, data
may be deleted from some partitions, etc.

Also, what would happen after a restart? If we lose the samples, we'll
have to resample everything anyway - and after a restart the system is
usually fairly busy, so that's not a great timing.

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

>> sample might be used for estimation of clauses directly.
> You mean, to use them in difficult cases, such of estimation of grouping
> over APPEND ?

That's one example, yes. But the sample might be used even to estimate
complex conditions on a single partition (there's plenty of stuff we
can't estimate from MCV/histogram).

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

Well, a separate catalog is one of the options. But I don't see how that
deals with large samples, etc.

regards

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