Re: Partitioning and postgres_fdw optimisations for multi-tenancy

On 2020-07-16 19:35, Etsuro Fujita wrote:
> On Thu, Jul 16, 2020 at 8:56 PM Andrey Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
>> On 7/16/20 9:55 AM, Etsuro Fujita wrote:
> 
>> >>>> On Tue, Jul 14, 2020 at 12:48 AM Alexey Kondratov
>> >>>> <a.kondratov@postgrespro.ru> wrote:
>> >>>>> Some real-life test queries show, that all single-node queries aren't
>> >>>>> pushed-down to the required node. For example:
>> >>>>>
>> >>>>> SELECT
>> >>>>>       *
>> >>>>> FROM
>> >>>>>       documents
>> >>>>>       INNER JOIN users ON documents.user_id = users.id
>> >>>>> WHERE
>> >>>>>       documents.company_id = 5
>> >>>>>       AND users.company_id = 5;
> 
>> > PWJ cannot be applied
>> > to the join due to the limitation of the PWJ matching logic.  See the
>> > discussion started in [1].  I think the patch in [2] would address
>> > this issue as well, though the patch is under review.
> 
>> I think, discussion [1] is little relevant to the current task. Here 
>> we
>> join not on partition attribute and PWJ can't be used at all.
> 
> The main point of the discussion is to determine whether PWJ can be
> used for a join between partitioned tables, based on
> EquivalenceClasses, not just join clauses created by
> build_joinrel_restrictlist().  For the above join, for example, the
> patch in [2] would derive a join clause "documents.company_id =
> users.company_id" from an EquivalenceClass that recorded the knowledge
> "documents.company_id = 5" and "users.company_id = 5", and then the
> planner would consider from it that PWJ can be used for the join.
> 

Yes, it really worked well. Thank you for the explanation, it wasn't so 
obvious for me as well. That way, I think that the patch from [1] covers 
many cases of joins targeting a single partition / foreign server.

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.


Regards
-- 
Alexey Kondratov

Postgres Professional https://www.postgrespro.com
Russian Postgres Company