Hash Right join and seq scan

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

Responses

Browse pgsql-performance by date

  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