Re: Partitioning and postgres_fdw optimisations for multi-tenancy

On 7/16/20 9:55 AM, Etsuro Fujita wrote:
> On Wed, Jul 15, 2020 at 9:02 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
>> On Wed, Jul 15, 2020 at 12:12 AM Alexey Kondratov
>> <a(dot)kondratov(at)postgrespro(dot)ru> wrote:
>>> On 2020-07-14 15:27, Ashutosh Bapat wrote:
>>>> On Tue, Jul 14, 2020 at 12:48 AM Alexey Kondratov
>>>> <a(dot)kondratov(at)postgrespro(dot)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.
>

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

>
> [1] https://www.postgresql.org/message-id/CAN_9JTzo_2F5dKLqXVtDX5V6dwqB0Xk%2BihstpKEt3a1LT6X78A%40mail.gmail.com
> [2] https://www.postgresql.org/message-id/502.1586032678@sss.pgh.pa.us
>
>

--
regards,
Andrey Lepikhov
Postgres Professional