Re: Merge David and Goliath tables efficiently

> 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