Re: Hash Right join and seq scan

[James Pang <jamespang886@xxxxxxxxx>]

   the join is  "left out join" 

James Pang <jamespang886@xxxxxxxxx> 於 2024年7月3日週三 下午2:51寫道：

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_33

Thanks,

James