From: | nicolas paris <nicolas(dot)paris(at)riseup(dot)net> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Merge David and Goliath tables efficiently |
Date: | 2023-06-20 10:02:17 |
Message-ID: | 63977a990ee6f3fbbedf4ca78e289591fe38d8a3.camel@riseup.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2023-06-20 12:45:30 | Re: Merge David and Goliath tables efficiently |
Previous Message | Les | 2023-06-20 07:18:31 | Re: Index on (fixed size) bytea value |