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

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

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