evaluate scalar UDF in plan rewriter

phil2

Hello

I see that builtin functions and scalar sql functions are replaced with constants when examining plan with explain. Is it possible to make scalar UDF wich will be evaluated and replaced with constant in plan rewrier like builtin functions do?

Example query:
select item_id from dma.item_parameters where carbrand_id = get_param_id('BMW')

Now I'm getting something like:
+-STORAGE ACCESS for item_parameters [Cost: 387K, Rows: 598M] (PATH ID: 1)
| Projection: DMA.item_parameters_b0
| Materialize: item_parameters.item_id
| Filter: (item_parameters.carbrand_id = public.get_param_id('BMW'))
| Execute on: All Nodes

And I want to get this:
+-STORAGE ACCESS for item_parameters [Cost: 374K, Rows: 18M] (PATH ID: 1)
| Projection: DMA.item_parameters_b0
| Materialize: item_parameters.item_id
| Filter: (item_parameters.carbrand_id = 42)
| Execute on: All Nodes

So is it possible to make plan rewriter evaluate scalar UDF so it can be used as constant predicate and affect row estimation properly so I can get a better plan during joins?

phil2

Figured out I just need to specify volatility in UDF

Ariel_Cary

Correct. The volatility of your UDF needs to be stable or immutable to do constant folding.