Re: Partitioning and postgres_fdw optimisations for multi-tenancy

On 2020-07-16 14:56, Andrey Lepikhov wrote:
> On 7/16/20 9:55 AM, Etsuro Fujita wrote:
>> On Wed, Jul 15, 2020 at 9:02 PM Etsuro Fujita 
>> <etsuro.fujita@gmail.com> wrote:
>>> On Wed, Jul 15, 2020 at 12:12 AM Alexey Kondratov
>>> <a.kondratov@postgrespro.ru> wrote:
>>>> On 2020-07-14 15:27, Ashutosh Bapat 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;
>>>>> 
>>>>> There are a couple of things happening here
>>>>> 1. the clauses on company_id in WHERE clause are causing partition
>>>>> pruning. Partition-wise join is disabled with partition pruning 
>>>>> before
>>>>> PG13.
>>> 
>>> More precisely, PWJ cannot be applied when there are no matched
>>> partitions on the nullable side due to partition pruning before PG13.
>> 
>> On reflection, I think I was wrong: the limitation applies to PG13,
>> even with advanced PWJ.
>> 
>>> But the join is an inner join, so I think PWJ can still be applied 
>>> for
>>> the join.
>> 
>> I think I was wrong in this point as well :-(.  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.
>> 

Thanks for sharing the links, Fujita-san.

> 
> 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. Here
> we can use push-down join of two foreign relations.
> We can analyze baserestrictinfo's of outer and inner RelOptInfo's and
> may detect that only one partition from outer and inner need to be
> joined.
> Next, we will create joinrel from RelOptInfo's of these partitions and
> replace joinrel of partitioned tables. But it is only rough outline of
> a possible solution...
> 

I was a bit skeptical after eyeballing the thread [1], but still tried 
v3 patch with the current master and my test setup. Surprisingly, it 
just worked, though it isn't clear for me how. With this patch 
aforementioned simple join is completely pushed down to the foreign 
server. And speedup is approximately the same (~3 times) as when 
required partitions are explicitly used in the query.

As a side-effected it also affected join + aggregate queries like:

SELECT
     user_id,
     count(*) AS documents_count
FROM
     documents
     INNER JOIN users ON documents.user_id = users.id
WHERE
     documents.company_id = 5
     AND users.company_id = 5
GROUP BY
     user_id;

With patch it is executed as:

  GroupAggregate
    Group Key: documents.user_id
    ->  Sort
          Sort Key: documents.user_id
          ->  Foreign Scan
                Relations: (documents_node2 documents)
                            INNER JOIN (users_node2 users)

Without patch its plan was:

  GroupAggregate
    Group Key: documents.user_id
    ->  Sort
          Sort Key: documents.user_id
          ->  Hash Join
                Hash Cond: (documents.user_id = users.id)
                ->  Foreign Scan on documents_node2 documents
                ->  Hash
                      ->  Foreign Scan on users_node2 users

I cannot say that it is most efficient plan in that case, since the 
entire query could be pushed down to the foreign server, but still it 
gives a 5-10% speedup on my setup.


Regards
-- 
Alexey Kondratov

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