Horton

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

Rafia Sabih <rafia.sabih@enterprisedb.com>

View thread View on postgr.es
From: Rafia Sabih <rafia.sabih@enterprisedb.com>
To: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Robert Haas <robertmhaas@gmail.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-21T06:12:28Z
Lists: pgsql-hackers

Attachments

On Thu, Jul 20, 2017 at 8:53 AM, Thomas Munro <thomas.munro@enterprisedb.com
> wrote:

> 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
>
>
Following the discussion at [1], with the patch Thomas posted there, now
Q21 completes in some 160 seconds. The plan is changed for the good but
does not use partition-wise join. The output of explain analyse is
attached.

Not just the join orders but the join strategy itself changed, with the
patch no hash semi join is picked which was consuming most time there,
rather nested loop semi join is in picture now, though the estimates are
still way-off, but the change in join-order made them terrible from
horrible. It appears like this query is performing efficient now
particularly because of worse under-estimated hash-join as compared to
under-estimated nested loop join.

For the hash-semi-join:
->  Hash  (cost=3449457.34..3449457.34 rows=119994934 width=8) (actual
time=180858.448..180858.448 rows=119994608 loops=3)
                                                   Buckets: 33554432
 Batches: 8  Memory Usage: 847911kB

Overall, this doesn't look like a problem of partition-wise join patch
itself.

[1]
https://www.postgresql.org/message-id/CAEepm%3D3%3DNHHko3oOzpik%2BggLy17AO%2Bpx3rGYrg3x_x05%2BBr9-A%40mail.gmail.com


-- 
Regards,
Rafia Sabih
EnterpriseDB: 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".