Hash Right join and seq scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



 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 

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux