September 2, 2025
Summary: In this tutorial, you will learn various types of scan “nodes” / operations that you might see in explain plans.
Table of Contents
Function Scan
Example:
explain analyze select * from generate_series(1,10) i;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series i (cost=0.00..10.00 rows=1000 width=4) (actual time=0.012..0.013 rows=10 loops=1)
Total runtime: 0.034 ms
(2 rows)
Generally it’s so simple, and does not need to be explained, but for completeness reasons, it is briefly introduced here.
Function Scan, is very simple node – it runs a function that returns recordset – that is, it will not run function like “lower()", but a function that returns (at least potentially) multiple rows, or multiple columns. After the function will return rows, these are returned to whatever is above Function Scan in plan tree, or to client, if Function Scan is the top node.
The only additional logic it might have, is ability to filter returned rows, like in here:
explain analyze select * from generate_series(1,10) i where i < 3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series i (cost=0.00..12.50 rows=333 width=4) (actual time=0.012..0.014 rows=2 loops=1)
Filter: (i < 3)
Rows Removed by Filter: 8
Total runtime: 0.030 ms
(4 rows)
Values Scan
Just like previously mentioned Result node, Values Scan is for returning simple, entered in query, data, but this time – it can be whole recordset, based on VALUES() functionality.
In case you don’t know, you can select multiple rows with multiple columns, without any table, just by using VALUES syntax, like here:
select * from ( values (1, 'redrock'), (2, 'postgres'), (3, 'postgresql') ) as t (a,b);
a | b
---+------------
1 | redrock
2 | postgres
3 | postgresql
(3 rows)
Such query plan looks like:
QUERY PLAN
--------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36)
(1 row)
It is also most commonly used in INSERTs, but it has other uses too, like custom sorting.
CTE Scan
This is similar to previously mentioned Materialized operation. It runs a part of a query, and stores output so that it can be used by other part (or parts) of the query.
Example:
explain analyze with x as (select relname, relkind from pg_class)
select relkind, count(*), (select count(*) from x)
from x
group by relkind;
1. QUERY PLAN
2.-----------------------------------------------------------------------------------------------------------------
3. HashAggregate (cost=24.80..26.80 rows=200 width=1) (actual time=0.466..0.468 rows=6 loops=1)
4. CTE x
5. -> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=65) (actual time=0.009..0.127 rows=295 loops=1)
6. InitPlan 2 (returns $1)
7. -> Aggregate (cost=6.57..6.58 rows=1 width=0) (actual time=0.085..0.085 rows=1 loops=1)
8. -> CTE Scan on x x_1 (cost=0.00..5.84 rows=292 width=0) (actual time=0.000..0.055 rows=295 loops=1)
9. -> CTE Scan on x (cost=0.00..5.84 rows=292 width=1) (actual time=0.012..0.277 rows=295 loops=1)
10. Total runtime: 0.524 ms
11.(8 rows)
Please note that pg_class is scanned only once – line #5. But it’s results are stored in “x", and then scanned twice – inside aggregate (line #8) and HashAggregate (line #9).
How is it different from Materialize? The difference stems from simple fact that CTE’s are user defined. While Materialize is helper operation that Pg chooses to use when (it thinks) it makes sense.
The very important thing is that CTEs are ran just as specified. So they can be used to circumvent some not-so-good optimizations that planner normally can do.