Re: Partial aggregates pushdown

On 10/15/21 21:31, Stephen Frost wrote:
> Greetings,
>
> * Tomas Vondra (tomas(dot)vondra(at)enterprisedb(dot)com) wrote:
>> On 10/15/21 17:05, Alexander Pyhalov wrote:
>>> Tomas Vondra писал 2021-10-15 17:56:
>>>> And then we should extend this for aggregates with more complex
>>>> internal states (e.g. avg), by supporting a function that "exports"
>>>> the aggregate state - similar to serial/deserial functions, but needs
>>>> to be portable.
>>>>
>>>> I think the trickiest thing here is rewriting the remote query to call
>>>> this export function, but maybe we could simply instruct the remote
>>>> node to use a different final function for the top-level node?
>>>
>>> If we have some special export function, how should we find out that
>>> remote server supports this? Should it be server property or should it
>>> somehow find out it while connecting to the server?
>>
>> Good question. I guess there could be some initial negotiation based on
>> remote node version etc. And we could also disable this pushdown for older
>> server versions, etc.
>
> Yeah, I'd think we would just only support it on versions where we know
> it's available. That doesn't seem terribly difficult.
>

Yeah.

But maybe Alexander was concerned about cases where the nodes disagree
on the aggregate definition, so one node might have the export function
and the other would not. E.g. the remote node may have older version of
an extension implementing the aggregate, without the export function
(although the server version supports it). I don't think we can do much
about that, it's just one of many issues that may be caused by
mismatching schemas.

I wonder if this might get more complex, though. Imagine for example a
partitioned table on node A with a FDW partition, pointing to a node B.
But on B, the object is partitioned again, with one partition placed on
C. So it's like

A -> partition on B -> partition on C

When planning on A, we can consider server version on B. But what if C
is an older version, not supporting the export function?

Bot sure if this makes any difference, though ... in the worst case it
will error out, and we should have a way to disable the feature on A.

>> But after that, I think we can treat this just like other definitions
>> between local/remote node - we'd assume they match (i.e. the remote server
>> has the export function), and then we'd get an error if it does not. If you
>> need to use remote nodes without an export function, you'd have to disable
>> the pushdown.
>>
>> AFAICS this works both for case with explicit query rewrite (i.e. we send
>> SQL with calls to the export function) and implicit query rewrite (where the
>> remote node uses a different finalize function based on mode, specified by
>> GUC).
>
> Not quite sure where to drop this, but I've always figured we'd find a
> way to use the existing PartialAgg / FinalizeAggregate bits which are
> used for parallel query when it comes to pushing down to foreign servers
> to perform aggregates. That also gives us how to serialize the results,
> though we'd have to make sure that works across different
> architectures.. I've not looked to see if that's the case today.
>

It sure is similar to what serial/deserial functions do for partial
aggs, but IIRC the functions were not designed to be portable. I think
we don't even require compatibility across minor releases, because we
only use this to copy data between workers running at the same time. Not
saying it can't be made to work, of course.

> Then again, being able to transform an aggregate into a partial
> aggregate that runs as an actual SQL query would mean we do partial
> aggregate push-down against non-PG FDWs and that'd be pretty darn neat,
> so maybe that's a better way to go, if we can figure out how.
>
> (I mean, for avg it's pretty easy to just turn that into a SELECT that
> grabs the sum and the count and use that.. other aggregates are more
> complicated though and that doesn't work, maybe we need both?)
>

Maybe, but that seems like a very different concept - transforming the
SQL so that it calculates different set of aggregates that we know can
be pushed down easily. But I don't recall any other practical example
beyond the AVG() -> SUM()/COUNT(). Well, VAR() can be translated into
SUM(X), SUM(X^2).

Another thing is how many users would actually benefit from this. I
mean, for this to matter you need partitioned table with partitions
placed on a non-PG FDW, right? Seems like a pretty niche use case.

regards

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