> 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
