the join is "left out join" James Pang <jamespang...@gmail.com> 於 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 <jamespang...@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 >> >
