Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables - Mailing list pgsql-hackers

From Rafia Sabih
Subject Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables
Date
Msg-id CAOGQiiP4DLDkOV8mShtsbG1oye5KFnSbpbXO3FqNGX9sZB1wDA@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables  (Thomas Munro <thomas.munro@enterprisedb.com>)
Responses Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables
List pgsql-hackers

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.


--
Regards,
Rafia Sabih
Attachment

pgsql-hackers by date:

Previous
From: Neha Sharma
Date:
Subject: Re: [HACKERS] [TRAP: FailedAssertion] causing server to crash
Next
From: Michael Paquier
Date:
Subject: Re: [HACKERS] Error while copying a large file in pg_rewind