August 26, 2025
Summary: In this tutorial, you will learn various types of control “nodes” / operations that you might see in explain plans.
Table of Contents
Limit
I used limit many times, because it’s so simple, but let’s describe it fully. Limit operation runs it’s sub-operation, and returns just first N rows from what it returned. Usually it also stops sub-operation afterwards, but in some cases (PL/PgSQL functions for example), the sub-operation is already finished when it returned first row.
Simple example:
explain analyze select * from pg_class;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=203) (actual time=0.008..0.047 rows=295 loops=1)
Total runtime: 0.096 ms
(2 rows)
explain analyze select * from pg_class limit 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.07 rows=2 width=203) (actual time=0.009..0.010 rows=2 loops=1)
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=203) (actual time=0.008..0.009 rows=2 loops=1)
Total runtime: 0.045 ms
(3 rows)
As you can see using limit in the 2nd case caused underlying Seq Scan to finish it’s work immediately after finding two rows.
Append
This plan simply runs multiple sub-operations, and returns all the rows that were returned as one resultset.
This is used by UNION/UNION ALL queries:
explain select oid from pg_class union all select oid from pg_proc union all select oid from pg_database;
QUERY PLAN
-----------------------------------------------------------------
Append (cost=0.00..104.43 rows=2943 width=4)
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=4)
-> Seq Scan on pg_proc (cost=0.00..92.49 rows=2649 width=4)
-> Seq Scan on pg_database (cost=0.00..1.02 rows=2 width=4)
(4 rows)
In here you can see append running three scans on three tables and returning all the rows together.
Please note that I used UNION ALL. If I’d used UNION, we would get:
explain select oid from pg_class union select oid from pg_proc union select oid from pg_database;
QUERY PLAN
-----------------------------------------------------------------------
HashAggregate (cost=141.22..170.65 rows=2943 width=4)
-> Append (cost=0.00..133.86 rows=2943 width=4)
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=4)
-> Seq Scan on pg_proc (cost=0.00..92.49 rows=2649 width=4)
-> Seq Scan on pg_database (cost=0.00..1.02 rows=2 width=4)
(5 rows)
This is because UNION removes duplicate rows – which is, in this case, done using HashAggregate operation.
InitPlan
This plan happens whenever there is a part of your query that can (or have to) be calculated before anything else, and it doesn’t depend on anything in the rest of your query.
For example, let’s assume you’d want such query:
explain select * from pg_class where relkind = (select relkind from pg_class order by random() limit 1);
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=13.11..24.76 rows=73 width=203)
Filter: (relkind = $0)
InitPlan 1 (returns $0)
-> Limit (cost=13.11..13.11 rows=1 width=1)
-> Sort (cost=13.11..13.84 rows=292 width=1)
Sort Key: (random())
-> Seq Scan on pg_class pg_class_1 (cost=0.00..11.65 rows=292 width=1)
(7 rows)
In this case – getting the limit/sort/seq-scan is needed to run before normal seq scan on pg_class – because PostgreSQL will have to compare relkind value with the value returned by subquery.
On the other hand, if I’d write:
explain select *, (select length('redrock')) from pg_class;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on pg_class (cost=0.01..10.93 rows=292 width=203)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=0)
(3 rows)
PostgreSQL correctly sees that the subselect column does not depend on any data from pg_class table, so it can be run just once, and doesn’t have to redo the length-calculation for every row.
Of course you can have many init plans, like in here:
explain select *, (select length('redrock')) from pg_class where relkind = (select relkind from pg_class order by random() limit 1);
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=13.12..24.77 rows=73 width=203)
Filter: (relkind = $1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=0)
InitPlan 2 (returns $1)
-> Limit (cost=13.11..13.11 rows=1 width=1)
-> Sort (cost=13.11..13.84 rows=292 width=1)
Sort Key: (random())
-> Seq Scan on pg_class pg_class_1 (cost=0.00..11.65 rows=292 width=1)
(9 rows)
There is one important thing, though – numbering of init plans within single query is “global", and not “per operation".
SubPlan
SubPlans are a bit similar to NestedLoop. In this way that these can be called many times.
SubPlan is called to calculate data from a subquery, that actually does depends on current row.
For example:
explain analyze select c.relname, c.relkind, (Select count(*) from pg_Class x where c.relkind = x.relkind) from pg_Class c;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on pg_class c (cost=0.00..3468.93 rows=292 width=65) (actual time=0.135..26.717 rows=295 loops=1)
SubPlan 1
-> Aggregate (cost=11.83..11.84 rows=1 width=0) (actual time=0.090..0.090 rows=1 loops=295)
-> Seq Scan on pg_class x (cost=0.00..11.65 rows=73 width=0) (actual time=0.010..0.081 rows=93 loops=295)
Filter: (c.relkind = relkind)
Rows Removed by Filter: 202
Total runtime: 26.783 ms
(7 rows)
For every row that is returned by scan on “pg_class as c", PostgreSQL has to run SubPlan, which checks how many rows in pg_class have the same (as currently processed row) value in relkind column.
Please note “loops=295" in the “Seq Scan on pg_class x" line, and matching “rows=295" in the earlier “Seq Scan on pg_class c" node.