On 2020-07-16 19:35, Etsuro Fujita wrote:
On Thu, Jul 16, 2020 at 8:56 PM Andrey Lepikhov
<a.lepik...@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.kondra...@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
