Re: Hash Right join and seq scan

Sorry for confusion, it's from attached explain output of the SQL.
please check attached. my questions is : for nestloop of two partition
tables , they use same partition key and equal join on partition key, the
cost could be "outer tables estimated rows" * (average index scan of only
one partition of inner table) , instead of "outer tables estimated rows"
* (index scans of all partitions), is it possible ? or it's still need
running time partition pruning enhancement?
random_page_cost = 1.1, seq_page_cost=1.0,
effective_cache_size=0.75*physical memory size. set random_page_cost=0.9
make optimizer to choose index scan instead of seq scan.

Thanks,

James

David Rowley <dgrowleyml(at)gmail(dot)com> 於 2024年7月6日週六 上午8:33寫道：

> On Sat, 6 Jul 2024 at 02:43, James Pang <jamespang886(at)gmail(dot)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
>