Optimizing common table expressions and planner support functions

Common table expressions (CTEs) are often used to make queries more readable. However, in older versions (pre 12), CTEs came with some PostgreSQL specifics. The result of a CTE is always calculated—just like an independent query. The optimizer could not inline the query and turn it into something faster. A CTE was an optimization barrier that greatly limited the freedom of the planner to do smart things. In PostgreSQL, the situation has changed. The optimizer is now a lot smarter and has more options to deal with CTEs in general. 

Let's take a look at an example. The following plan was created in PostgreSQL 11:

test=# explain WITH x AS (SELECT * FROM generate_series(1, 5) AS id)
 SELECT * FROM x;
 QUERY PLAN 
---------------------------------------------------------------------------
 CTE Scan on x (cost=10.00..30.00 rows=1000 width=4)
 CTE x
 -> Function Scan on generate_series id (cost=0.00..10.00 rows=1000 width=4)
(3 rows)

As you can see, PostgreSQL executes CTE as is and scans the result. But there's more: the planner estimates that the generate_series function returns 1000 rows, which is, of course, not true.

Let's take a look at the plan produced by PostgreSQL 12:

test=# explain WITH x AS (SELECT * FROM generate_series(1, 5) AS id)
 SELECT * FROM x;
 QUERY PLAN 
-----------------------------------------------------------------------
 Function Scan on generate_series id (cost=0.00..0.05 rows=5 width=4)
(1 row)

There are two things we can see here. First of all, the CTE scan is gone, which means that PostgreSQL simply inlined the CTE and optimized it away. But there's more. Let's take a closer look at the number of rows estimated. In this case, the estimate is correct. The reason for that is support functions. When writing a function, we can provide PostgreSQL with an additional function providing estimates to the optimizer:

test=# \h CREATE FUNCTION
Command:  CREATE FUNCTION
Description: define a new function
Syntax:
CREATE [ OR REPLACE ] FUNCTION
...
 | SUPPORT support_function
... 
URL: https://www.postgresql.org/docs/12/sql-createfunction.html

This is incredibly useful if you are dealing with set-returning functions returning thousands or even millions of rows at a time. By telling the optimizer what to expect, it can make smarter decisions, which is, of course, beneficial to overall performance.