From: | Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> |
---|---|
To: | Alexey Kondratov <a(dot)kondratov(at)postgrespro(dot)ru> |
Cc: | Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Partitioning and postgres_fdw optimisations for multi-tenancy |
Date: | 2020-07-16 04:55:48 |
Message-ID: | CAPmGK15dTPxjL___pnxVfkMNfVTf91nKdHAkVVHu7KJey8OBWA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
Best regards,
Etsuro Fujita
[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
From | Date | Subject | |
---|---|---|---|
Next Message | Noah Misch | 2020-07-16 04:56:39 | Re: renaming configure.in to configure.ac |
Previous Message | tsunakawa.takay@fujitsu.com | 2020-07-16 04:53:38 | RE: Transactions involving multiple postgres foreign servers, take 2 |