On Thu, Jul 20, 2017 at 8:53 AM, Thomas Munro <thomas.mu...@enterprisedb.com > wrote:
> On Thu, Jul 20, 2017 at 2:02 PM, Robert Haas <robertmh...@gmail.com> > wrote: > > On Wed, Jul 19, 2017 at 7:45 PM, Thomas Munro > > <thomas.mu...@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/
Q21_SE_patch.out
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
