Going to forward my response from an individual thread with Jeff here in case anyone else can help me out.
I wasn't sure if forwarding the message to the mailing list would be considered as a continuation of the same subject, so I'm just going to paste it here. I'm a bit of an email noob :P
-------------------------------------------------------------------------------------------------------------------------
Jeff,
First off, thanks for the thoughtful response.
@ the first point about write locks
I think I had/have a misconception about how inserts work in postgres. It's my understanding that postgres will never draft a parallel insert plan for any query (except maybe CREATE TABLE AS?) because the process needs to acquire an exclusive access write lock to the table it is inserting on. My thinking was that since the partitions are treated as separate tables that they can be theoretically inserted to in parallel.
@ the second point about indexes on textfield
I realized my error on this after I sent the email, indexes do not speed up large joins, just small ones.
@ the third point about hash joins
So this is interesting to me. Your description of how hash joins work sounds like the behaviour I would want, yet performing huge joins is where my larger databases have been getting stuck. Upon looking back at my code, I think I realize perhaps why they were getting stuck. So my database doesn't have just one table, it has three principal tables which relate to one another: Full disclosure, these databases/tables are distributed between multiple machines and can get quite enormous (some tables individually are 200+GB)
tab1 (textfield1 text, tf1_id bigint) unique on textfield1
tab1 (textfield1 text, tf1_id bigint) unique on textfield1
tab2 (textfield2 text, tf2_id bigint) unique on textfield2
tab3 (tf1_id_fk bigint, tf2_id_fk bigint) unique on tf1_id_fk, tf2_id_fk
So as I'm uploading new data (in the form of (textfield1, textfield2) entries) I need to record the ID of each matching record on the join(s) or null if there was no match (thus a new record). The way I have been accomplishing this so far has been like so:
1. create temporary table upload(textfield1 text, textfield2 text, tf1_id bigint, tf2_id bigint);
2. copy :'source' to upload(textfield1, textfield2);
3. update upload set tf1_id = tab1.tf1_id from tab1 where upload.textfield1 = tab1.textfield1;
4. create temporary table new_textfield1 (textfield1 text, tf1_id bigint);
5. insert into new_textfield1 (select distinct on (textfield1) textfield1, nextval('tf1_id_sequence') as tf1_id from upload where tf1_id is null)
6. update upload u set tf1_id = ntf1.tf1_id from new_textfield1 ntf1 where u.tf1_id is null and u.textfield1 = ntf1.textfield1;
-- etc. continue process for tab2, tab3
Now, since I wrote that code I've learned about aggregation & window functions so I can generate the new ids during the big table join rather than after the fact, but the big join "update" statement has been where the process gets stuck for huge tables. I notice that the query planner generates a different strategy when just selecting data vs "insert select" or "update".
For example, when I write a query to join entries from one huge table to another using a select statement, I get a nice parallel hash join plan like you mention.
explain select * from hugetable1 join hugetable2 on some_field;
Results in:
Gather
Workers Planned: 2
-> Parallel Hash Left Join
Hash Cond: ((ht1.some_field)::text = (ht2.some_field)::text)
-> Parallel Append
-> Parallel Seq Scan on hugetable2 ht2
However, when I change this to an update or insert select, I get a very different plan.
explain insert into dummy1 (select * from hugetable1 join hugetable2 on some_field)
OR
explain update hugetable1 ht1 set id = ht2.id from hugetable2 ht2 where ht1.some_field = ht2.some_field
Results in:
Update on hugetable1 ||OR|| Insert on dummy1
-> Merge Join
Merge Cond: ((ht1.some_field)::text = (ht2.some_field)::text)
-> Sort
Sort Key: ht1.some_field
-> Seq Scan on hugetable1 ht1
-> Materialize
-> Sort
Sort Key: ht2.some_field
-> Append
-> Seq Scan on hugetable2 ht2
Maybe this query should perform similarly to the hash join, but the fact remains that I've had databases stuck for weeks on plans like this. The partitioning strategy I've adopted has been an attempt to force data locality during the join operation, and so far has been working reasonably well. If you have some insight into why these large update/insert operations go so slowly, it would be much appreciated.
-Ben
On Sun, Apr 17, 2022 at 11:50 AM Alvaro Herrera <alvherre@xxxxxxxxxxxxxx> wrote:
On 2022-Apr-14, Benjamin Tingle wrote:
> It doesn't help if I partition temp_data by textfield beforehand either
> (using the same scheme as the target table). It still opts to concatenate
> all of temp_data, hash it, then perform a sequential scan against the
> target partitions.
Does it still do that if you set
SET enable_partitionwise_join TO on;
? If the partition strategies are identical, that might get you a
better plan. (Actually, in pg13 and upwards the strategies don't need
to be exactly identical, just "compatible".)
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Thou shalt not follow the NULL pointer, for chaos and madness await
thee at its end." (2nd Commandment for C programmers)
Ben(t).
