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

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

> On Thu, Jul 20, 2017 at 2:02 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
> wrote:
> > On Wed, Jul 19, 2017 at 7:45 PM, Thomas Munro
> > <thomas(dot)munro(at)enterprisedb(dot)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/