Re: Partition-wise join for join between (declaratively) partitioned tables

On Wed, Jul 26, 2017 at 11:08 AM, Rafia Sabih
<rafia(dot)sabih(at)enterprisedb(dot)com> wrote:
>
>
> On Wed, Jul 26, 2017 at 11:06 AM, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>>
>> On Wed, Jul 26, 2017 at 11:00 AM, Rafia Sabih
>> <rafia(dot)sabih(at)enterprisedb(dot)com> wrote:
>> >
>> >
>> > On Wed, Jul 26, 2017 at 10:58 AM, Ashutosh Bapat
>> > <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>> >>
>> >> On Tue, Jul 25, 2017 at 11:01 AM, Rafia Sabih
>> >> <rafia(dot)sabih(at)enterprisedb(dot)com> wrote:
>> >>
>> >> > Query plans for the above mentioned queries is attached.
>> >> >
>> >>
>> >> Can you please share plans for all the queries, even if they haven't
>> >> chosen partition-wise join when run on partitioned tables with
>> >> enable_partition_wise_join ON? Also, please include the query in
>> >> explain analyze output using -a or -e flats to psql. That way we will
>> >> have query and its plan in the same file for ready reference.
>> >>
>> > I didn't run the query not using partition-wise join, for now.
>>
>> parse-parse error, sorry. Do you mean, you haven't run the queries
>> which do not use partition-wise join?
>>
> Yes, that's what I mean.

Ok. If those queries have equi-join between partitioned tables and are
not picking up partition-wise join, that case needs to be
investigated. Q21 for example has join between three lineitem
instances. Those joins can be executed by partition-wise join. But it
may so happen that optimal join order doesn't join partitioned tables
with each other, thus interleaving partitioned tables with
unpartitioned or differently partitioned tables in join order.
Partition-wise join is not possible then. A different partitioning
scheme may be required there.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company