Thread: Hash Right join and seq scan

 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 

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@gmail.com> 於 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 

Hi James,

I think it'd be much easier to help you with investigating this issue if
you shared the actual queries, and the full EXPLAIN ANALYZE output both
with and without disabled hashjoin. Or even better, share a script that
reproduces the issue (creates tables, loads data, runs the queries).

BTW you suggested each partition has ~250k rows, but the explain plan
snippet you shared does not seem to be consistent with that - it only
shows 2500-5000 rows per partition. If you run ANALYZE on the table,
does that change the plan?

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Sat, 6 Jul 2024 at 02:43, James Pang <jamespang886@gmail.com> wrote:
>            for nest loop path, since the first one estimated only "8" rows , and they use partitionkeyid as joinkey and all are hash partitions , is it better to estimate cost to  8 (loop times) * 1600 = 12800 (each one loop  map to only 1 hash partition bitmap scan ,avg one partition cost), that's much less than 398917.29 of all partitions ?

I'm not really sure where you're getting the numbers from here. The
outer side of the deepest nested loop has an 8 row estimate, not the
nested loop itself.  I'm unsure where the 1600 is from. I only see
1669.

As of now, we don't do a great job of costing for partition pruning
that will happen during execution.  We won't be inventing anything to
fix that in existing releases of PostgreSQL, so you'll need to either
adjust the code yourself, or find a workaround.

You've not shown us your schema, but perhaps enable_partitionwise_join
= on might help you. Other things that might help are further lowering
random_page_cost or raising effective_cache_size artificially high.
It's hard to tell from here how much random I/O is being costed into
the index scans.  You could determine this by checking if the nested
loop plan costs change as a result of doing further increases to
effective_cache_size. You could maybe nudge it up enough for it to win
over the hash join plan. It is possible that this won't work, however.

>  for secondary Nest Loop Anti join could be rows 299118 rows *  15.78(avg index scan cost of one partition) = 4,720,082 that still much less than  132168227.57 ?
>          for Hash Right join, is it possible to estimate by  8 seq partition scan instead of all 32 hash partitions since the first query estimated 8 rows only ?
>          extend statistics may help estimate count(partitionkeyid) based on other columns bind variables, but looks like that did not help table join case.

I can't quite follow this. You'll need to better explain where you're
getting these numbers for me to be able to understand.

David