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

On Thu, Mar 16, 2017 at 12:35 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Mar 15, 2017 at 8:55 AM, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>> Sorry. That was added by my patch to refactor
>> set_append_rel_pathlist(). I have added a patch in the series to
>> remove that line.
>
> It's not worth an extra commit just to change what isn't broken.
> Let's just leave it alone.

Ok. Removed that patch from the set of patches.

>
>>> Very sad. I guess if we had parallel append available, we could maybe
>>> dodge this problem, but for now I suppose we're stuck with it.
>>
>> Really sad. Is there a way to look at the relation (without any
>> partial paths yet) and see whether the relation will have partial
>> paths or not. Even if we don't have actual partial paths but know that
>> there will be at least one added in the future, we will be able to fix
>> this problem.
>
> I don't think so. If we know that rel->consider_parallel will end up
> true for a plain table, we should always get a parallel sequential
> scan path at least, but if there are foreign tables involved, then
> nothing is guaranteed.

Ok.

>
>>> partition_wise_plan_weight may be useful for testing, but I don't
>>> think it should be present in the final patch.
>>
>> partition_join test needs it so that it can work with smaller dataset
>> and complete faster. For smaller data sets the partition-wise join
>> paths come out to be costlier than other kinds and are never chosen.
>> By setting partition_wise_plan_weight I can force partition-wise join
>> to be chosen. An alternate solution would be to use
>> sample_partition_fraction = 1.0, but then we will never test delayed
>> planning for unsampled child-joins. I also think that users will find
>> partition_wise_plan_weight useful when estimates based on samples are
>> unrealistic. Obviously, in a longer run we should be able to provide
>> better estimates.
>
> I still don't like it -- we have no other similar knob.

We have another cost-skewing GUC, disable_cost, which adds a huge cost
to anything that needs to be disabled. This is different in the sense
that it multiplies the cost.

Well, in that case, we can replace it with force_partition_wise_plan
(on/off) for the sake of regression, to test with smaller data. Even
then we will need to adjust the costs, so that partition-wise join
plan comes out to be the cheapest. Probably we will need set
partition-wise join plan costs to very low or even 0 when
force_partition_wise_plan is set to on. Does that look good? Any other
ideas?

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