Re: Merge David and Goliath tables efficiently

I came here to talk about partitionwise join, but then noticed you have
already thought of that:

On 2023-Jun-18, nicolas paris wrote:

> Note that both plan acome from the same partitioned by hash table with
> 100 parts, with a unique index on the list_id + hash_key. For strategy
> 2.1, I turned on enable_partitionwise_join, since david table has the
> same partitioning scheme as goliath including unique indexe. In both
> case the query is:

Hmm, I suppose the reason partitionwise join isn't having any effect is
that the presence of WHEN NOT MATCHED clauses force an outer join, which
probably disarms partitionwise joining, since each join pair would
require to match for nulls, so there would be two matching partitions at
the other end. A quick test for this hypothesis might be to try the
MERGE without the WHEN NOT MATCHED clauses and see if partitionwise join
works better.

Maybe Tom L's new outer-join infrastructure in 16 allows to improve on
this, not sure.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Los dioses no protegen a los insensatos. Éstos reciben protección de
otros insensatos mejor dotados" (Luis Wu, Mundo Anillo)