CODEWITHSUNDEEP
postgresqlcommon-table-expressionsql-function
ma11hew28
ma11hew28

Reputation: 126477

Are multiple calls to a STABLE function optimized across CTEs?

According to PostgreSQL: Documentation: 38.7. Function Volatility Categories:

A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. This category allows the optimizer to optimize multiple calls of the function to a single call.

What about when using CTEs? If I call a STABLE function inside a CTE and then again inside the primary SELECT query, will the optimizer optimize both calls of the function to a single call?

How can you tell? (I don't know how to use EXPLAIN.)

In the impractical example below, I want to make sure that the function get_user_by_id() is only called once.

CREATE TABLE users (
  id bigserial PRIMARY KEY,
  username varchar(64) NOT NULL
);

CREATE FUNCTION get_user_by_id(_id bigint) RETURNS users AS $$
  SELECT *
  FROM users
  WHERE id = _id
$$ LANGUAGE SQL STABLE;

INSERT INTO users (username) VALUES ('user1');

WITH error AS (
  SELECT -1 AS code
  WHERE (SELECT get_user_by_id(3) IS NULL)
)
SELECT code AS id, NULL AS username FROM error
UNION ALL
SELECT * FROM get_user_by_id(3) WHERE id IS NOT NULL;

Upvotes: 0

Views: 74

Answers (1)

ma11hew28
ma11hew28

Reputation: 126477

I think the answer is no, multiple calls to a STABLE function are not optimized across CTEs. I don't know how to prove it, but according to PostgreSQL: Documentation: 11: 7.8. WITH Queries (Common Table Expressions), CTEs seem to be separate "auxiliary statements". And, they don't seem to be well-integrated with the parent query. For example:

. . . the optimizer is less able to push restrictions from the parent query down into a WITH query than an ordinary subquery. The WITH query will generally be evaluated as written, without suppression of rows that the parent query might discard afterwards.

Upvotes: 0

Related Questions