Re: Partitioning and postgres_fdw optimisations for multi-tenancy

On Fri, Jul 17, 2020 at 10:00 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
>
> On Sat, Jul 18, 2020 at 12:44 AM Ashutosh Bapat
> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> > On Fri, Jul 17, 2020 at 8:24 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> > > On Fri, Jul 17, 2020 at 1:56 AM Alexey Kondratov
> > > <a(dot)kondratov(at)postgrespro(dot)ru> wrote:
> > > > However, there is an issue with aggregates as well. For a query like:
> > > >
> > > > SELECT
> > > > count(*)
> > > > FROM
> > > > documents
> > > > WHERE
> > > > company_id = 5;
> > > >
> > > > It would be great to teach planner to understand, that it's a
> > > > partition-wise aggregate as well, even without GROUP BY company_id,
> > > > which doesn't always help as well. I'll try to look closer on this
> > > > problem, but if you have any thoughts about it, then I'd be glad to
> > > > know.
> > >
> > > The reason why the aggregation count(*) isn't pushed down to the
> > > remote side is: 1) we allow the FDW to push the aggregation down only
> > > when the input relation to the aggregation is a foreign (base or join)
> > > relation (see create_grouping_paths()), but 2) for your case the input
> > > relation would be an append relation that contains the foreign
> > > partition as only one child relation, NOT just the foreign partition.
> > > The resulting Append path would be removed in the postprocessing (see
> > > [1]), but that would be too late for the FDW to do the push-down work.
> > > I have no idea what to do about this issue.
> >
> > Won't partitionwise aggregate push aggregate down to partition and
> > then from there to the foreign server through FDW?
>
> Sorry, my words were not clear. The aggregation above is count(*)
> *without GROUP BY*, so we can’t apply PWA to it.

Ok. Thanks for the clarification.

IIRC, if GROUP BY does not contain the partition key, partition-wise
aggregate will collect partial aggregates from each partition and then
combine those to form the final aggregate. However, we do not have
infrastructure to request partial aggregates from a foreign server (we
lack SQL level support for it). Hence it's not pushed down to the
foreign server. For count(*) there is no difference between full and
partial aggregates so it appears as if we could change PARTIAL to FULL
to push the aggregate down to the foreign server but that's not true
in general.

--
Best Wishes,
Ashutosh Bapat