Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

evaluate scalar UDF in plan rewriter


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?


  • Figured out I just need to specify volatility in UDF

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

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.