Composite types in PostgreSQL: (func()).* - Hidden performance issues

04.2017 / Category: / Tags:

PostgreSQL support cases are coming in on a regular basis. This week an especially noteworthy one reached our desks here at Cybertec, which is so interesting, that I decided to sit down and share some information. I guess many people out there have similar issues and therefore this post might be helpful to developers and administrators out there.

Composite data types in PostgreSQL

PostgreSQL offers a very interesting feature: It is possible to create so called “composite types”, which are simply a collection of fields..

Here is an example:

In this example a type called “sample_type” is created. For the sake of simplicity it consists of three integer values. The beauty of PostgreSQL is that composite types can be used just like normal types (e.g. as columns, array, etc.). The use of composite types offers database developers a great deal of flexibility. Passing more complex data to functions is a lot easier than without composite types and therefore composites are a joy to work with.

However, when using a composite type it is also necessary to be cautious and to understand the basic inner workings of PostgreSQL.

Consider the following function:

The function returns two rows and sleeps for two seconds. So far things are fine.

However, consider the following trivial SQL statement:

As you can see the statement takes around 6 seconds. The reason is simple: The code is executed three times. Note that there is just one function call. However, PostgreSQL does something important behind the scenes: It expands the SELECT-clause. To PostgreSQL “(sample_func()).*” is not just a single call. It expands things before execution in a way that the function is called three times (one call for each column). While this is not a big deal for fast, tiny functions, it can be a HUGE issue for more complicated or slow code such as the function I have just written.

Reducing the number of function calls

To reduce the number of function calls the function can be moved to the FROM-clause of the query:

The execution time has dropped by 2/3 and the code only emits one NOTICE.

Additional performance tips

Sometimes it is hard to simply move the composite type to a FROM-clause. In those special cases it can make sense to work with subselects and NOTICE messages for debugging to get around the problem. Usually trying multiple variations of the query can lead to success.

If you have any questions regarding this issue, feel free to contact us and to leave a comment in the feedback section. 

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
David Johnston
David Johnston
7 years ago

The use of LATERAL greatly reduces the need for formal subqueries to solve this problem - its lack, when the function arguments had to come from table columns, was the main contributor to writing set-returning functions in the select list.

7 years ago

This is pretty annoying and I stumble into it while trying to write a set-returning function that take a single jsonb as argument.

My goal was to write an "unwrapper" that can transform json to a native postgresql composite type.

Thus this function can be applied in anywhere without problem. But when using the (func()).* syntax the function is executed once by column and it will be very hard for the end user to understand why.

This is especially confusing because my function is declared as immutable and so you would expect that the planner would execute it only once. I had to find a comment in the documentation that finally pointed out that (as of now) "it's not a bug, it's a feature".

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram