> This is absolutely expected. If you partition by hash (id, part_key),
> you can't join on (id) and expect partitionwise join to work. To
> quote
> the enable_partitionwise_join documentation [1]:
>
> Enables or disables the query planner's use of partitionwise
> join,
> which allows a join between partitioned tables to be performed by
> joining the matching partitions. Partitionwise join currently
> applies only when the join conditions include all the partition
> keys, which must be of the same data type and have one-to-one
> matching sets of child partitions.
>
> So the fact that
>
> merge into goliath using david on david.id = goliath.id
> when matched then update set val = david.val
> when not matched then insert (id, val) values (david.id,
> david.val);
>
> does not work is absolutely expected. You need to join on part_col
> too.
Definitely this makes sense to add the part_col in the join columns.
Also it helps the planner to choose a better plan, since now it goes
with per partition nested loop without having to trick the costs
(either enable_hashjoin/random_page_cost), with my current workload so
far.
Thanks you goliath
-- david