Horton

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

Thomas Munro <thomas.munro@enterprisedb.com>

View thread View on postgr.es
From: Thomas Munro <thomas.munro@enterprisedb.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Rafia Sabih <rafia.sabih@enterprisedb.com>, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-07-20T03:23:44Z
Lists: pgsql-hackers 
On Thu, Jul 20, 2017 at 2:02 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Jul 19, 2017 at 7:45 PM, Thomas Munro
> <thomas.munro@enterprisedb.com> wrote:
>> Isn't this the same as the issue reported here?
>>
>> https://www.postgresql.org/message-id/flat/CAEepm%3D270ze2hVxWkJw-5eKzc3AB4C9KpH3L2kih75R5pdSogg%40mail.gmail.com
>
> Hmm, possibly.  But why would that affect the partition-wise join case only?

It doesn't.  From Rafia's part_reg.zip we see a bunch of rows=1 that
turn out to be wrong by several orders of magnitude:

21_nopart_head.out:  Hash Semi Join  (cost=5720107.25..9442574.55
rows=1 width=50)
21_part_head.out:    Hash Semi Join  (cost=5423094.06..8847638.36
rows=1 width=38)
21_part_patched.out: Hash Semi Join  (cost=309300.53..491665.60 rows=1 width=12)

My guess is that the consequences of that bad estimate are sensitive
to arbitrary other parameters moving around, as you can see from the
big jump in execution time I showed in the that message, measured on
unpatched master of the day:

  4 workers = 9.5s
  3 workers = 39.7s

That's why why both parallel hash join and partition-wise join are
showing regressions on Q21: it's just flip-flopping between various
badly costed plans.  Note that even without parallelism, the fix that
Tom Lane suggested gives a much better plan:

https://www.postgresql.org/message-id/CAEepm%3D11BiYUkgXZNzMtYhXh4S3a9DwUP8O%2BF2_ZPeGzzJFPbw%40mail.gmail.com

-- 
Thomas Munro
http://www.enterprisedb.com

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".