Horton

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

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>

View thread View on postgr.es
From: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-03-16T10:51:14Z
Lists: pgsql-hackers 
On Thu, Mar 16, 2017 at 12:35 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Mar 15, 2017 at 8:55 AM, Ashutosh Bapat
> <ashutosh.bapat@enterprisedb.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

Commits

  1. Basic partition-wise join functionality.

  2. Assorted preparatory refactoring for partition-wise join.

  3. Teach adjust_appendrel_attrs(_multilevel) to do multiple translations.

  4. Stamp 10beta2.

  5. Eat XIDs more efficiently in recovery TAP test.

  6. Abstract logic to allow for multiple kinds of child rels.

  7. Implement SortSupport for macaddr data type

  8. Attempt to stabilize grouping sets regression test plans.

  9. Teach xlogreader to follow timeline switches

  10. Don't scan partitioned tables.

  11. Fix grammar.

  12. postgres_fdw: Push down FULL JOINs with restriction clauses.

  13. Some preliminary refactoring towards partitionwise join.

  14. contrib/amcheck needs RecentGlobalXmin to be PGDLLIMPORT'ified.

  15. Print test parameters like "foo: 123", and results like "foo = 123".