Re: Partial join
| От | Richard Guo |
|---|---|
| Тема | Re: Partial join |
| Дата | |
| Msg-id | CAN_9JTxQtJr2RHLKAfDCJoZFF+qsUMAF5JF+BVyAVcmcC2kSQA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Partial join (Arne Roland <A.Roland@index.de>) |
| Ответы |
Re: Partial join
Re: Partial join |
| Список | pgsql-hackers |
On Thu, Aug 1, 2019 at 5:38 PM Arne Roland <A.Roland@index.de> wrote:
Hello,
I attached one example of a partitioned table with multi column partition key. I also attached the output.
Disabling the hash_join is not really necessary, it just shows the more drastic result in the case of low work_mem.
Comparing the first and the second query I was surprised to see that SET enable_partitionwise_join could cause the costs to go up. Shouldn't the paths of the first query be generated as well?
The third query seems to have a different issue. That one is close to my original performance problem. It looks to me like the push down of the sl condition stops the optimizer considering a partial join.
If so would it be sane to keep a copy of the original quals to make the partial join possible? Do you have better ideas?
For the third query, a rough investigation shows that, the qual 'sl =
5' and 'sc.sl = sg.sl' will form an equivalence class and generate two
implied equalities: 'sc.sl = 5' and 'sg.sl = 5', which can be pushed
down to the base rels. One consequence of the deduction is when
constructing restrict lists for the joinrel, we lose the original
restrict 'sc.sl = sg.sl', and this would fail the check
have_partkey_equi_join(), which checks if there exists an equi-join
condition for each pair of partition keys. As a result, this joinrel
would not be considered as an input to further partitionwise joins.
5' and 'sc.sl = sg.sl' will form an equivalence class and generate two
implied equalities: 'sc.sl = 5' and 'sg.sl = 5', which can be pushed
down to the base rels. One consequence of the deduction is when
constructing restrict lists for the joinrel, we lose the original
restrict 'sc.sl = sg.sl', and this would fail the check
have_partkey_equi_join(), which checks if there exists an equi-join
condition for each pair of partition keys. As a result, this joinrel
would not be considered as an input to further partitionwise joins.
We need to fix this.
Thanks
Richard
В списке pgsql-hackers по дате отправления: