Why Does `(func()).*` in PostgreSQL Cause Multiple Function Evaluations?

*PostgreSQL Performance Issue: The `(func()).` Syntax and Redundant Function Calls**

This article examines a performance problem in PostgreSQL related to the (func()).* syntax when used with functions returning composite types or sets. The original observation, shown in the query below, highlights the unexpected behavior:

<code class="language-sql">SELECT (func(3)).*; -- Leads to multiple function calls</code>

The Problem: Excessive Function Evaluations

The core issue is that (func()).* triggers a separate function call for each column in the function's output. A function returning four columns, for example, might result in eight function calls instead of the anticipated two. This contrasts sharply with alternative syntax, such as:

<code class="language-sql">SELECT N, func(N); -- More efficient approach</code>

Solution: Efficient Query Rewriting

To circumvent the excessive calls, a subquery provides a workaround. While generally effective, this isn't a perfect solution and might introduce other performance considerations.

For PostgreSQL 9.3 and later, the LATERAL keyword offers a superior solution:

<code class="language-sql">SELECT mf.*
FROM some_table
LEFT JOIN LATERAL my_func(some_table.x) AS mf ON true;</code>

Root Cause: PostgreSQL Parser Behavior

The root cause lies in how PostgreSQL's parser handles the * wildcard within the (func()).* construct. The wildcard expansion into individual columns during parsing is the source of the redundant function calls.

Performance Benchmark and Demonstration

A custom function example demonstrates the performance discrepancy between the problematic syntax and the suggested workarounds. Tests show that the subquery approach (or a CTE) offers significant performance improvements.

Conclusion: Optimizing Queries in PostgreSQL

While the multiple function call issue with (func()).* remains a known behavior, the workarounds, especially using LATERAL (PostgreSQL 9.3 ), provide effective strategies for developers to optimize query performance and reduce unnecessary function evaluations.

The above is the detailed content of Why Does `(func()).*` in PostgreSQL Cause Multiple Function Evaluations?. For more information, please follow other related articles on the PHP Chinese website!