From: | James Pang <jamespang886(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Hash Right join and seq scan |
Date: | 2024-07-03 04:57:08 |
Message-ID: | CAHgTRffB3SYu2oYYZSWCcF7Vf0AUU_QH-EDA8GeUCbJ5yyZZ-g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Both tables are hash partition tables , and we have a left out join ,
optimizer convert to Hash Right Join, but it always try to seq scan on
tablexxx 32 paritions. there are almost 250k rows per parition for
tablexxxx , so it's slow. As a workaround, I disable hashjoin the it run
much fast with index scan on tablexxxx ,nestloop join.
With Hash Right Join, optimizer always use seq scan for outer table ?
PGv13.11
-> Hash Right Join (cost=22.50..6760.46 rows=5961 width=78)
Hash Cond: ((aa.partitionkeyid)::text = (b_9.paritionkeyid)::text)
-> Append (cost=0.00..6119.48 rows=149032 width=79)
-> Seq Scan on tablexxxx_p0 aa_2 (cost=0.00..89.71
rows=2471 width=78)
-> Seq Scan on tablexxxx_p1 aa_3 (cost=0.00..88.23
rows=2423 width=78)
-> Seq Scan on tablexxxx_p2 aa_4 (cost=0.00..205.26
rows=5726 width=79)
-> Seq Scan on tablexxxx_p3 aa_5 (cost=0.00..102.92
rows=2892 width=78)
-> Seq Scan on tablexxxx_p4 aa_6 (cost=0.00..170.27
rows=4727 width=78)
...
-> Seq Scan on tablexxxx_p31 aa_33 (cost=0.00..220.59
rows=6159 width=79)
-> Append (cost=0.69..187.64 rows=4034 width=78) (actual
time=0.030..0.035 rows=3 loops=3)
index scan .... tableyyyy_p0 b_2
index scan ..... tableyyyy_p1 b_3
....
index scan ... tableyyyy_p31 b_33
Thanks,
James
From | Date | Subject | |
---|---|---|---|
Next Message | James Pang | 2024-07-03 06:51:54 | Re: Hash Right join and seq scan |
Previous Message | Andrei Lepikhov | 2024-07-01 11:31:24 | Re: a lot of shared buffers hit when planning for a simple query with primary access path |