the query is
select ....
from tableyyyy b join table xxxx aa
on b.partitionkeyid=aa.partitionkeyid
where b.id1= $1 and b.id2=$2 and b.rtime between $3 and $4;
looks like optimizer try to "calculate cost for nestloop for scanning all partitions of tablexxx (32 hash partitions) " but actually , it only scan only a few partitions. that make the nestloop cost more than hashjoin with table seq scan cost. optimizer does not the partitioney passed in by tableyyy that got selected based on indexes on other columns. possible to make optimizer to calculate cost with partition pruning? since the join key is hash partition key .
Thanks,
James
James Pang <jamespang886@xxxxxxxxx> 於 2024年7月3日週三 下午12:57寫道：
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_33Thanks,James
