Name: System Design Masterclass
Availability: InStock

A view is a rewrite the planner can see through. A function is code, and how much the planner can see through it depends on two things the author chose: the volatility category and the language.

Both are one word, both are usually left at the default, and both change the plan by orders of magnitude.

Two panels. Declared VOLATILE, which is the default: may differ on every call, so it is called once per row and no index can be used, measuring 143.874 ms with a sequential scan. Declared STABLE: constant within a statement, so it is called once and the index is used, measuring 0.078 ms with an index only scan. — ComparisonFigure 1. Identical code, identical arguments, identical answer, and one thousand eight hundred times
A real PostgreSQL, a million rows, and one function written three times with only the volatility keyword changed.

The volatility category is a promise the author makes to the planner about whether the same arguments always give the same answer. The planner believes it completely, and everything about how many times the function runs follows from it.

A terminal capture of the functions lab. The first table shows the same function body in a WHERE clause: VOLATILE at 143.874 ms, STABLE at 0.078 and IMMUTABLE at 0.080. The plan for VOLATILE is an Aggregate over a Seq Scan on events with the function in the filter; the plan for STABLE is an Aggregate over an Index Only Scan with the function as an index condition. The second section shows a VOLATILE function on a column at 70.975 ms against the same body IMMUTABLE with an expression index at 0.087 ms. The third shows an expression written out at 34.712 ms, as a SQL function at 86.254 and as a PL/pgSQL function at 206.290, with both plans differing from the written-out one. — terminal-figureFigure 2. The measurement. A real PostgreSQL, a million rows, one function body
Every timing is the execution time reported by EXPLAIN ANALYZE against a database this lab started and tore down.

A sequence diagram between the planner, the function and the table. The planner asks what a VOLATILE function returns and is told possibly something different every time, so it must call it per row and scan everything, since an index needs a value and there is not one. It asks what a STABLE function returns and is told the same thing for the whole statement, so it calls it once and looks it up. — SequenceFigure 3. An index lookup needs a value, and a VOLATILE function has none to give
To use an index the planner must know what it is looking for before it starts. A function that may return something different on every call cannot supply that.

A bar chart in milliseconds for one query over a million rows. VOLATILE takes 143.874 ms, STABLE takes 0.078 and IMMUTABLE takes 0.080. — ChartFigure 4. A function created without a volatility category is VOLATILE
Which means the tall bar is what you get by not saying anything. Most functions in most schemas are declared without a category, and most of them would be correct as STABLE or IMMUTABLE.

Declare the category on every function you write, and declare it honestly. It costs one word, it is the single largest lever in this lesson, and leaving it out chooses the most pessimistic option available.

Database Functions

The rest of this lesson

Continue this lesson

One Word In The Definition

What The Plans Say