From: nicolas paris <nicolas.paris@riseup.net>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Merge David and Goliath tables efficiently
Date: Tue, 20 Jun 2023 12:02:17 +0200
Message-ID: <63977a990ee6f3fbbedf4ca78e289591fe38d8a3.camel@riseup.net> (raw)
In-Reply-To: <ee9a5c6e-c4a2-44e8-4c77-4fa5ce7b31cc@enterprisedb.com>
References: <20230619074646.2zlxxaqjieiafgqi@alvherre.pgsql>
<27cdb9c5-6dc7-87ff-303e-b0d0d20edbc4@enterprisedb.com>
<dc4115489cd1bbddab1fea3cf0eeb8bdd06657e7.camel@riseup.net>
<36cb9fe9-b792-2345-65ed-586de44a48b9@enterprisedb.com>
<45316372426111f4b1dffa6de420804da3befa3d.camel@riseup.net>
<ee9a5c6e-c4a2-44e8-4c77-4fa5ce7b31cc@enterprisedb.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nicolas.paris@riseup.net, pgsql-performance@lists.postgresql.org
Subject: Re: Merge David and Goliath tables efficiently
In-Reply-To: <63977a990ee6f3fbbedf4ca78e289591fe38d8a3.camel@riseup.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox