Options

evaluate scalar UDF in plan rewriter

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?

Comments

  • Options

    Figured out I just need to specify volatility in UDF

  • Options
    Ariel_CaryAriel_Cary Vertica Employee Employee

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file