From: | "Andrey V(dot) Lepikhov" <a(dot)lepikhov(at)postgrespro(dot)ru> |
---|---|
To: | Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> |
Cc: | Alexey Kondratov <a(dot)kondratov(at)postgrespro(dot)ru>, 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-17 04:23:19 |
Message-ID: | 91498942-7aff-d5da-89d1-3614d9ffc0ac@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 7/16/20 9:35 PM, Etsuro Fujita wrote:
> On Thu, Jul 16, 2020 at 8:56 PM Andrey Lepikhov
> <a(dot)lepikhov(at)postgrespro(dot)ru> wrote:
>> On 7/16/20 9:55 AM, Etsuro Fujita 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;
>
>>> 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.
>
Ok, this patch works and you solved a part of the problem with this
interesting approach.
But you can see that modification of the query:
SELECT * FROM documents, users WHERE documents.company_id = 5 AND
users.company_id = 7;
also can be pushed into node2 and joined there but not.
My point is that we can try to solve the whole problem.
--
regards,
Andrey Lepikhov
Postgres Professional
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2020-07-17 05:01:21 | Re: expose parallel leader in CSV and log_line_prefix |
Previous Message | Noah Misch | 2020-07-17 03:40:13 | Re: Which SET TYPE don't actually require a rewrite |