C Extension: Is it faster to return a set with ValuePerCall or Materialize mode?

All we need is an easy explanation of the problem, so here it is.

PostgreSQL documents it’s Set Returning Functions with C extensions like this,

C-language functions have two options for returning sets (multiple rows).

  • In one method, called ValuePerCall mode, a set-returning function is called repeatedly (passing the same arguments each time) and it returns one new row on each call, until it has no more rows to return and signals that by returning NULL. The set-returning function (SRF) must therefore save enough state across calls to remember what it was doing and return the correct next item on each call.
  • In the other method, called Materialize mode, an SRF fills and returns a tuplestore object containing its entire result; then only one call occurs for the whole result, and no inter-call state is needed.

If you’re optimizing for execution speed, which is faster ValuePerCall or Materialize mode? Obviously Materialize mode will take more memory but it seems like it should faster if your goal is to return a whole set, I don’t see that documented anywhere though.

These seem to be documented in the code as Set Returning Function Mode SFRM_ValuePerCall and SFRM_Materialize.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I benchmarked this pretty thoroughly with my projects. Here is what I found,

  • For resultsets fits in work_mem Materialize in a FROM clause.
  • For large sets it is better to use ValuePerCell in a SELECT list, and for sets with a LIMIT.

You can see my benchmarks in my repo pg-srf-repeat-benchmark

There is also SFRM_Materialize_Preferred which allows you to write a function that accepts a hint from the caller and does both.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply