This section summarizes the plan nodes returned by the
EXPLAIN
command.
For information on parallel scan nodes, see Section 15.3.1.
Scan nodes fetch data from indexes, tables, and table-compatible structures, such as set-returning functions.
Common scans that fetch data from tables and indexes.
Seq Scan
#
A Sequential Scan reads every table row in its
physical order on disk from start to end, returning results based on
query-provided filters.
EXPLAIN SELECT * FROM tab1 WHERE id > 1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tab1 (cost=0.00..25.88 rows=423 width=36)
Filter: (id > 1)
Index Scan
#Traverses an index to find the exact tuple identifiers (TIDs) matching the index conditions and fetches those specific entries directly from the table heap.
EXPLAIN SELECT id, message FROM tab1 WHERE id = 1;
QUERY PLAN
-------------------------------------------------------------
Index Scan using tab1_pkey on tab1 (cost=0.15..2.37 rows=1 width=36)
Index Cond: (id = 1)
Index Only Scan
#Fetches data directly from an index, avoiding the need to scan over table pages.
Because indexes do not store transaction visibility information directly,
an index-only scan references a table's
visibility map first. If a page is
marked as not all-visible, such as when one of its rows was recently
modified but not yet committed, the scan must still check the
corresponding page (Heap Fetch) to ensure
MVCC
compliance.
EXPLAIN ANALYZE SELECT id FROM tab1 WHERE id = 5;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Only Scan using tab1_pkey on tab1 (cost=0.29..1.40 rows=1 width=4) (actual time=0.052..0.053 rows=1 loops=1)
Index Cond: (id = 5)
Heap Fetches: 1
Bitmap scans are performed using several nodes in multiple stages:
(optional) BitmapORs and/or BitmapANDs
EXPLAIN ANALYZE SELECT id FROM tab1 WHERE id > 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tab1 (cost=2156.51..5931.51 rows=199999 width=4) (actual time=9.558..36.428 rows=199999 loops=1)
Recheck Cond: (id > 1)
Rows Removed by Index Recheck: 1
Heap Blocks: exact=758 lossy=517
-> Bitmap Index Scan on tab1_pkey (cost=0.00..2106.51 rows=199999 width=0) (actual time=9.450..9.451 rows=199999 loops=1)
Index Cond: (id > 1)
Bitmap Index Scan
#Scans an index for matching rows in the corresponding table. However, unlike an Index Scan, which fetches rows, it instead builds a bitmap (an array of 1s and 0s) where each bit represents a row or page of the table.
If possible, it will create an exact
bitmap: each element corresponds to a specific tuple in the heap.
If the bitmap cannot be fit into the memory space allocated by
work_mem, it will transition to
creating a lossy bitmap, which tracks not the exact
tuples, but the table pages containing them. Lossy bitmaps require the
database to Recheck rows based on a
Recheck Condition during a
Bitmap Heap Scan.
BitmapOR
#An intermediate operation that combines bitmaps from Bitmap Index Scans by bitwise ORing them together.
BitmapAND
#An intermediate operation that combines bitmaps from Bitmap Index Scans by bitwise ANDing them together.
Bitmap Heap Scan
#Retrieves a bitmap produced by a Bitmap Index Scan or bitwise operation (BitmapOR or BitmapAND).
For an exact bitmap, it fetches the matching tuples directly from the table. For a lossy bitmap, it reads the referenced heap pages and rechecks their tuples to find matches.
Values are read in bitmap order, reducing random I/O and improving performance, predominantly on spinning hard drives.
These are scans used to review data outside the typical table/index structure or to rely on specialized Postgres features.
Result
#Returns constants or fixed calculations.
EXPLAIN SELECT 1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=4)
Furthermore, if the planner determines that a mandatory filter condition
is based on constant values, such as 1 = 2, the node
may be able to evaluate it as a One-Time Filter. If
the filter returns false, the planner may be able to
skip its child nodes.
EXPLAIN SELECT * FROM tab1 WHERE 1 = 2;
QUERY PLAN
-------------------------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
Foreign Scan
#Fetches data from a foreign table, defined by a foreign data wrapper (see Section 5.14 ).
EXPLAIN SELECT * FROM some_foreign_table;
QUERY PLAN
----------------------------------------------------------------
Foreign Scan on some_foreign_table (cost=100.00..410.30 rows=1365 width=36)
Custom Scan
#A non-native scan implemented by an extension (see Chapter 60 ).
Function Scan
#
Used when a function is referenced in
the
FROM
clause as if it were a table:
EXPLAIN SELECT * FROM generate_series(1, 1000);
QUERY PLAN
------------------------------------------------------------------------
Function Scan on generate_series (cost=0.00..10.00 rows=1000 width=4)
A function may utilize other nodes internally, such as sequential scans. These are omitted in the planner's output. To view the internal operations, you would have to load and configure the auto_explain module with auto_explain.log_nested_statements enabled.
ProjectSet
#
Referenced when a
set returning function, such as
generate_series()
, is called with a
SELECT
command rather than a SELECT ... FROM ...
command.
EXPLAIN SELECT generate_series(1, 1000);
QUERY PLAN
-------------------------------------------------
ProjectSet (cost=0.00..5.02 rows=1000 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
Values Scan
#
Reads a "constant table" defined with a
VALUES
list.
EXPLAIN SELECT *
FROM (
VALUES
('Optimism', 'Production bugs'),
('Trust', 'Rickrolled'),
('Leeroy Jenkins', 'Defeat')
) AS outcome(cause, outcome);
QUERY PLAN
--------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=64)
CTE Scan
#Fetches data from a Common Table Expression (see Section 7.8 ).
The CTE must be sufficiently complex. If possible, the planner may instead opt to collapse it into the broader query with a different scan (see Section 14.4)
Subquery Scan
#Fetches data from a subquery that is presented as a table.
The subquery must be sufficiently complex. If possible, the planner may instead opt to integrate it into the broader query with a different scan (see Section 14.4 ).
TID Scan
#
Every version of a row has a
CTID
(current tuple ID). It defines what table page a row is on and its
relative order within the page. For instance, a tuple ID of
(0, 5)
translates to "the fifth row (tuple) on the 0th page of a table".
This scan is used when filtering directly for a CTID.
EXPLAIN SELECT * FROM tab1 WHERE ctid = '(0, 1)'::tid;
QUERY PLAN
-----------------------------------------------------
Tid Scan on tab1 (cost=0.00..1.11 rows=1 width=22)
TID Cond: (ctid = '(0,1)'::tid)
Range TID Scan
#Same as a TID Scan, but instead of looking for a specific tuple, it retrieves tuples based on a range.
EXPLAIN SELECT ctid, * FROM tab1 WHERE ctid >= '(0, 1)'::tid AND ctid < '(1, 1)'::tid;
QUERY PLAN
----------------------------------------------------------------
Tid Range Scan on tab1 (cost=0.01..1.11 rows=1 width=28)
TID Cond: ((ctid >= '(0,1)'::tid) AND (ctid < '(1,1)'::tid))
WorkTable Scan
#
A scan on a CTE (see Section 7.8) generated within a
RECURSIVE
query.
EXPLAIN WITH RECURSIVE cte_name (id) AS (
SELECT 1
UNION ALL
SELECT id + 1
FROM cte_name
WHERE id < 5
)
SELECT * FROM cte_name;
QUERY PLAN
---------------------------------------------------------------------------------------
CTE Scan on cte_name (cost=2.65..3.27 rows=31 width=4)
CTE cte_name
-> Recursive Union (cost=0.00..2.65 rows=31 width=4)
-> Result (cost=0.00..0.01 rows=1 width=4)
-> WorkTable Scan on cte_name cte_name_1 (cost=0.00..0.23 rows=3 width=4)
Filter: (id < 5)
Table Function Scan
#When one uses either the JSON_TABLE or XMLTABLE functions, it will result in a Table Function Scan.
EXPLAIN SELECT *
FROM JSON_TABLE(
'{"name":"Alice"}',
'$'
COLUMNS (
name TEXT PATH '$.name'
)
) AS jt;
QUERY PLAN
-----------------------------------------------------------------------------
Table Function Scan on "json_table" jt (cost=0.01..1.00 rows=100 width=32)
Sample Scan
#
Returns a random sample of rows from a table when a
TABLESAMPLE
clause is present (see
Section 19.7.2)
EXPLAIN SELECT * FROM tab1 TABLESAMPLE SYSTEM (10);
QUERY PLAN
--------------------------------------------------------------
Sample Scan on tab1 (cost=0.00..340.80 rows=20000 width=22)
Sampling: system ('10'::real)
Named Tuplestore Scan
#
When a trigger is defined with
the
REFERENCING ... TABLE
clause, PostgreSQL creates a temporary table containing the
transition_relation_name
OLD and/or NEW rows produced by the
statement.
transition_relations are commonly used with triggers
that rely on
FOR EACH STATEMENT definitions rather than
FOR EACH ROW defintions.
When a query within a trigger's function reads from the
transition_relations, the planner returns a
Named Tuplestore Scan.
CREATE OR REPLACE FUNCTION process_rows()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
row RECORD;
BEGIN
FOR row IN
SELECT * FROM new_rows --<------ reference to transition_relation "new_rows"
LOOP
NULL;
END LOOP;
RETURN NULL;
END;
$$;
CREATE TRIGGER process_rows_trigger
AFTER UPDATE ON tab1
REFERENCING NEW TABLE AS new_rows --<---- declaring the transition_relation "new_rows"
FOR EACH STATEMENT
EXECUTE FUNCTION process_rows();
Because nodes within functions are not directly returned by
EXPLAIN
, the scan node can only observed with the
auto_explain module
when
auto_explain.log_nested_statements.
is enabled.
The example output below was generated with auto_explain.log_nested_statements and was modified to be consistent with the other examples.
-- "new_rows" is defined as a transition_relation within a trigger function
EXPLAIN SELECT * FROM new_rows;
QUERY PLAN
-----------------------------------------------------------------------------
SELECT * FROM new_rows Named Tuplestore Scan (cost=0.00..0.04 rows=2 width=4)
Joins together rows from tables or cross-references multiple tables to satisfy a filter.
Combines two tables together by cross-comparing join key columns
Nested Loop
#Compares every join column value in one table to every join key value in the other to find matches.
For example, if a scan on a TABLE 1
returns 10 rows and a scan on a TABLE 2 returns 100
rows, the join compares the first row from
TABLE 1 to all 100 rows in
TABLE 2, then the second row to all 100 rows, and so
on until every combination has been checked. In this example, that
results in
10 * 100
comparisons.
When one or both sides of the join are small, the strategy may be preferred because of its low startup cost, which may outweigh the overhead of more sophisticated join methods.
However, the join is often used as a fallback strategy when other methods seem untenable, or the database lacks enough statistics to make better decisions (see Section 14.2 ).
The node can also be presented with modifiers:
Nested Loop Left Join
Nested Loop Right Join
Nested Loop Full Join
EXPLAIN SELECT tab1.id FROM tab1
JOIN tab2 ON tab2.id = tab1.id;
QUERY PLAN
-----------------------------------------------------------------------------------
Nested Loop (cost=0.28..328.50 rows=1000 width=4)
-> Seq Scan on tab1 (cost=0.00..15.00 rows=1000 width=4)
-> Index Only Scan using tab2_id_idx on tab2 (cost=0.28..0.30 rows=1 width=4)
Index Cond: (id = tab1.id)
Hash Join
#A Hash Join uses a hash table to find matching columns.
One side's join key column is hashed into an in-memory hash table. The other side's join key column is then hashed and fed into the hash table to find matches.
The node can also be presented with modifiers:
Hash Left Join
Hash Right Join
Hash Full Join
EXPLAIN SELECT tab1.id FROM tab1
JOIN tab2 ON tab2.id = tab1.id;
QUERY PLAN
--------------------------------------------------------------------
Hash Join (cost=27.50..56.25 rows=1000 width=4)
Hash Cond: (tab1.id = tab2.id)
-> Seq Scan on tab1 (cost=0.00..15.00 rows=1000 width=4)
-> Hash (cost=15.00..15.00 rows=1000 width=4)
-> Seq Scan on tab2 (cost=0.00..15.00 rows=1000 width=4)
Merge Join
#A merge join works by comparing two join key columns in lockstep.
Before the join begins, both sides must be sorted on the join column, either because an index already provides that ordering, or because the planner has added an explicit sort step.
The algorithm then compares the first values from
JOIN_COL_1 against the values in
JOIN_COL_2. If the values match, the corresponding
rows are joined.
When they do not match, the side with the smaller value advances, since no future row on the larger side can match a value that has already been passed. This continues until one side is exhausted.
The planner is more likely to pick merge joins when the join columns are already ordered by an index, or when the inputs are large enough that the memory overhead of a hash join is not practical.
The node can also be presented with modifiers:
Merge Left Join
Merge Right Join
Merge Full Join
EXPLAIN SELECT tab1.id FROM tab1
JOIN tab2 ON tab2.id = tab1.id;
QUERY PLAN
---------------------------------------------------------------------------------------
Merge Join (cost=0.55..66.75 rows=1000 width=8)
Merge Cond: (tab1.id = tab2.id)
-> Index Only Scan using tab1_id_idx on tab1 (cost=0.28..25.88 rows=100000 width=8)
-> Index Only Scan using tab2_id_idx on tab2 (cost=0.28..25.88 rows=100000 width=8)
Some nodes, although not explicitly joins, are comparable in that they compare rows between independent tables.
Semi Join
#
When a query uses a subquery
with an
IN
,
EXISTS
, or their equivalent operator, PostgreSQL does not need to produce a
combined row from both sides. It only needs to confirm that at least one
matching row exists in the
subquery.
SELECT col1 FROM tab1
WHERE EXISTS (
SELECT 1 FROM tab2 WHERE col2 =tab1.col2
);
To do this, PostgreSQL uses a semi-join: it compares the join column from the outer query against the join column from the subquery. For each outer row, as soon as a match is found, that row is returned, and the remaining subquery rows are skipped. Allowing an early escape for reviewing rows can also reduce execution time.
Semi-joins correspond to their join counterparts:
Nested Loop Semi Join
Hash Semi Join
Merge Semi Join
EXPLAIN SELECT tab1.id FROM tab1
WHERE id IN (SELECT id FROM tab2);
QUERY PLAN
--------------------------------------------------------------------
Hash Semi Join (cost=27.50..56.25 rows=1000 width=4)
Hash Cond: (tab1.id = tab2.id)
-> Seq Scan on tab1 (cost=0.00..15.00 rows=1000 width=4)
-> Hash (cost=15.00..15.00 rows=1000 width=4)
-> Seq Scan on tab2 (cost=0.00..15.00 rows=1000 width=4)
Anti Join
#
Comparable to the Semi-Joins,
but correspond to
subqueries
with a
NOT IN
,
NOT EXISTS
, or equivalent operator.
Like semi-joins, anti-joins correspond to their join counterparts:
Nested Loop Anti Join
Hash Anti Join
Merge Anti Join
EXPLAIN SELECT tab1.id FROM tab1
WHERE NOT EXISTS (SELECT 1 FROM tab2 WHERE tab1.id = tab2.id);
QUERY PLAN
--------------------------------------------------------------------
Hash Anti Join (cost=27.50..46.25 rows=1 width=4)
Hash Cond: (tab1.id = tab2.id)
-> Seq Scan on tab1 (cost=0.00..15.00 rows=1000 width=4)
-> Hash (cost=15.00..15.00 rows=1000 width=4)
-> Seq Scan on tab2 (cost=0.00..15.00 rows=1000 width=4)
HashSetOp
#
Like Hash Joins, it relies on
hashing to compare rows between tables. It is used specifically with the
INTERSECT
and
EXCEPT
commands.
EXPLAIN SELECT * FROM tab1
INTERSECT
SELECT * FROM tab2;
QUERY PLAN
---------------------------------------------------------------------------------
HashSetOp Intersect (cost=0.00..65.00 rows=1000 width=8)
-> Append (cost=0.00..60.00 rows=2000 width=8)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..25.00 rows=1000 width=8)
-> Seq Scan on tab1 (cost=0.00..15.00 rows=1000 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..25.00 rows=1000 width=8)
-> Seq Scan on tab2 (cost=0.00..15.00 rows=1000 width=4)
SetOp
#
Like Merge Joins, it relies on
sorting to compare rows between tables. It is used specifically with the
INTERSECT
and
EXCEPT
commands.
EXPLAIN SELECT * FROM tab1
EXCEPT
SELECT * FROM tab2;
QUERY PLAN
------------------------------------------------------------------------------------------
SetOp Except (cost=25261.35..26155.35 rows=89400 width=8)
-> Sort (cost=25261.35..25708.35 rows=178800 width=8)
Sort Key: "*SELECT* 1".id
-> Append (cost=0.00..5364.00 rows=178800 width=8)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..2235.00 rows=89400 width=8)
-> Seq Scan on tab1 (cost=0.00..1341.00 rows=89400 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..2235.00 rows=89400 width=8)
-> Seq Scan on tab2 (cost=0.00..1341.00 rows=89400 width=4)
Aggregate nodes take a collection of values from a column and combine them
into a single result. There are many
aggregate functions, such as
COUNT, AVG, and
SUM.
Aggregate
#Takes a column value and runs an aggregate function against it while adding it to a running state. When all the values are reviewed, the final state is returned.
For example, if a SUM aggregate function were used
against the values
1, 2, and
3, it would first add
1 to the running state. Then it would add
2, and finally
3. The running state would then be
6, which would be returned by the node.
EXPLAIN SELECT SUM(amount) FROM tab1;
QUERY PLAN
------------------------------------------------------------------
Aggregate (cost=2502.00..2502.01 rows=1 width=8)
-> Seq Scan on tab1 (cost=0.00..2252.00 rows=100000 width=8)
HashAggregate
#
When a
GROUP BY
clause is present, PostgreSQL must first collect rows that share the same
grouping values.
A HashAggregate hashes each
Group Key as it reviews a row. It then looks for a
matching hash value in a hash table that it has populated with the groups
it has already seen.
If it finds a matching entry, it executes the aggregate function and updates that group's aggregate state.
If no matching entry exists, it creates a new aggregate state for the group, adds it to the hash table, and then executes the aggregate function.
The node is also used to find unique values for certain modifiers, such as
DISTINCT
.
EXPLAIN SELECT COUNT(category), users FROM tab1 GROUP BY users;
QUERY PLAN
-------------------------------------------------------------------
HashAggregate (cost=2224.88..2226.88 rows=200 width=12)
Group Key: users
-> Seq Scan on tab1 (cost=0.00..1665.92 rows=111792 width=12)
GroupAggregate
#
Similar to a HashAggregate, but instead of hashing the
GROUP BY
keys,
GroupAggregate relies on the input being sorted. Once
rows are ordered, either by an index scan or by an explicit
sort step, the node identifies
each group by comparing adjacent grouping values. As it reviews a row, it
applies the aggregate function and updates the group's state.
EXPLAIN SELECT COUNT(category), users FROM tab1 GROUP BY users;
QUERY PLAN
-------------------------------------------------------------------------
GroupAggregate (cost=12860.17..14610.17 rows=100000 width=12)
Group Key: users
-> Sort (cost=12860.17..13110.17 rows=100000 width=12)
Sort Key: users
-> Seq Scan on tab1 (cost=0.00..1548.00 rows=100000 width=12)
MixedAggregate
#
May be used when the
ROLLUP, CUBE, or
GROUPING SETS
clauses are present.
A MixedAggregate node is triggered when multiple group
keys are evaluated using varying grouping strategies:
Hash
Runs entries through a hash table to evaluate groups.
Sort
Groups rows sequentially from sorted data.
Plain
Applies a global aggregate against the entire table to compute the
empty grouping set ().
The query below computes one set of groups using a
brand
column, another using
size column, and a final group representing the entire
table:
SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
brand | size | sum
-------+------+-----
Foo | | 30
Bar | | 20
| L | 15
| M | 35
| | 50
EXPLAIN SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
QUERY PLAN
----------------------------------------------------------------------
MixedAggregate (cost=0.00..230.73 rows=401 width=44)
Hash Key: brand
Hash Key: size
Group Key: ()
-> Seq Scan on items_sold (cost=0.00..136.32 rows=7232 width=44)
Because separate internal Hash operations are paired
with a Plain (Group Key: ()), the
planner used a MixedAggregate node.
WindowAgg
#
Used when
window functions, such as
row_number, rank, and
lead, are present in a query.
The node requires its inputs to be ordered according to the window
definition, which is achieved when an index scan already provides the
required order or by an explicit
sort step. The window
definition’s
PARTITION BY columns must be used as major sort keys,
while the
ORDER BY columns act as minor sort keys.
Once ordered, PostgreSQL evaluates each window function over the frame of rows associated with each input row.
EXPLAIN SELECT
RANK() OVER (ORDER BY score DESC) AS player_ranking,
score,
participants
FROM players;
QUERY PLAN
------------------------------------------------------------------------
WindowAgg (cost=163.06..198.74 rows=2040 width=20)
-> Sort (cost=163.04..168.14 rows=2040 width=12)
Sort Key: score DESC
-> Seq Scan on players (cost=0.00..30.40 rows=2040 width=12)
Changes row data.
Delete
#
Occurs when someone runs a
DELETE
command.
EXPLAIN DELETE FROM characters
WHERE name = 'Saruman'
QUERY PLAN
-----------------------------------------------------------------
Delete on characters (cost=0.00..27.00 rows=0 width=0)
-> Seq Scan on characters (cost=0.00..27.00 rows=7 width=6)
Filter: (name = 'Saruman'::text)
Insert
#
Inserts new rows via an
INSERT
command.
EXPLAIN INSERT INTO characters (name) VALUES ('Gandalf the Gray');
QUERY PLAN
--------------------------------------------------------
Insert on characters (cost=0.00..0.01 rows=0 width=0)
-> Result (cost=0.00..0.01 rows=1 width=32)
Update
#
Applies when someone runs an
UPDATE
command.
EXPLAIN UPDATE characters
SET name = 'Gandalf the White'
WHERE name = 'Gandalf the Grey';
QUERY PLAN
------------------------------------------------------------------
Update on characters (cost=0.00..27.00 rows=0 width=0)
-> Seq Scan on characters (cost=0.00..27.00 rows=7 width=38)
Filter: (name = 'Gandalf the Grey'::text)
Merge
#
Appears when a
MERGE
command is used.
EXPLAIN MERGE INTO characters AS c
USING (
VALUES
('Smeagol', 'cursed'),
('Sauron', 'defeat'),
('Bilbo', 'victory')
) AS outcome(name, fate)
ON outcome.name = c.name
WHEN MATCHED AND outcome.fate = 'cursed' THEN
UPDATE SET
name = 'Gollum'
WHEN MATCHED AND outcome.fate = 'defeat' THEN
DELETE
WHEN NOT MATCHED AND outcome.fate = 'victory' THEN
INSERT (name)
VALUES ('Gandalf the White');
QUERY PLAN
---------------------------------------------------------------------------------
Merge on characters c (cost=0.08..28.97 rows=0 width=0)
-> Hash Right Join (cost=0.08..28.97 rows=20 width=126)
Hash Cond: (c.name = "*VALUES*".column1)
-> Seq Scan on characters c (cost=0.00..23.60 rows=1360 width=38)
-> Hash (cost=0.04..0.04 rows=3 width=152)
-> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=152)
When running parallel plans (e.g., parallel scans or parallel joins), a gather node collects the values from each operation and stitches them together into a single output.
Gather
#Combines parallel operations, but does not preserve sort order.
EXPLAIN SELECT * FROM tab1;
QUERY PLAN
-----------------------------------------------------------------------------
Gather (cost=0.00..69292.77 rows=9999510 width=4)
Workers Planned: 4
-> Parallel Seq Scan on tab1 (cost=0.00..69282.77 rows=2499878 width=4)
Gather Merge
#Combines parallel operations, but preserves sort order.
EXPLAIN SELECT * FROM tab1 ORDER BY id;
QUERY PLAN
-----------------------------------------------------------------------------------
Gather Merge (cost=374978.30..522327.98 rows=9999510 width=4)
Workers Planned: 4
-> Sort (cost=374978.24..381227.93 rows=2499878 width=4)
Sort Key: id
-> Parallel Seq Scan on tab1 (cost=0.00..69282.77 rows=2499878 width=4)
They combine rows obtained by different partitions. Also used to help with
certain operations that combine rows, such as
UNION and INTERSECT.
Append
#
Combines rows from scans against partitions, or for operations that stack
rows, such as UNION and INTERSECT.
Does not take into account sort order.
EXPLAIN SELECT * FROM partitioned_table;
QUERY PLAN
------------------------------------------------------------------------------------
Append (cost=0.00..53.90 rows=2260 width=44)
-> Seq Scan on part_1 partitioned_table_1 (cost=0.00..21.30 rows=1130 width=44)
-> Seq Scan on part_2 partitioned_table_2 (cost=0.00..21.30 rows=1130 width=44)
Merge Append
#Same as Append, but preserves sort order.
EXPLAIN SELECT * FROM partitioned_table ORDER BY id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Merge Append (cost=0.32..83.22 rows=2260 width=44)
Sort Key: partitiond_table.id
-> Index Scan using part_1_pkey on part_1 partitioned_table_1 (cost=0.15..30.30 rows=1130 width=44)
-> Index Scan using part_2_pkey on part_2 partitioned_table_2 (cost=0.15..30.30 rows=1130 width=44)
Recursive Union
#
Combines sets produced within
RECURSIVE
queries.
EXPLAIN WITH RECURSIVE cte_name (id) AS (
SELECT 1
UNION ALL
SELECT id + 1
FROM cte_name
WHERE id < 5
)
SELECT * FROM cte_name;
QUERY PLAN
---------------------------------------------------------------------------------------
CTE Scan on cte_name (cost=2.65..3.27 rows=31 width=4)
CTE cte_name
-> Recursive Union (cost=0.00..2.65 rows=31 width=4)
-> Result (cost=0.00..0.01 rows=1 width=4)
-> WorkTable Scan on cte_name cte_name_1 (cost=0.00..0.23 rows=3 width=4)
Filter: (id < 5)
These nodes perform hashes to support downstream operations, such as
Hash Joins
.
Hash
#Performs a hash against data. type.
EXPLAIN SELECT tab1.id FROM tab1
JOIN tab2 ON tab2.id = tab1.id;
QUERY PLAN
--------------------------------------------------------------------
Hash Join (cost=27.50..56.25 rows=1000 width=4)
Hash Cond: (tab1.id = tab2.id)
-> Seq Scan on tab1 (cost=0.00..15.00 rows=1000 width=4)
-> Hash (cost=15.00..15.00 rows=1000 width=4)
-> Seq Scan on tab2 (cost=0.00..15.00 rows=1000 width=4)
These nodes perform sorting, typically to support an
ORDER BY
clause or to enable downstream nodes, such as
WindowAggs
, that depend on ordered input.
Sort
#PostgreSQL can use the quicksort algorithm or, if only a few rows from are set, such as tht tope 10 are needed, the top-N heapsort algorithm to order values. For large enough entries, Postgres may opt to perform an external merge sort on disk.
EXPLAIN SELECT * FROM tab1 ORDER BY amount
QUERY PLAN
--------------------------------------------------------------
Sort (cost=179.78..186.16 rows=2550 width=4)
Sort Key: amount
-> Seq Scan on tab1 (cost=0.00..35.50 rows=2550 width=4)
Incremental Sort
#
If multiple columns are being sorted, such as in an
ORDER BY clause:
ORDER BY category, amount;
and if one or more leading columns are already ordered, for example, due to an index or a previous operation, PostgreSQL can perform an incremental sort.
Instead of sorting the entire dataset at once, an incremental sort relies
on the existing order of the leading columns (
Presorted Keys) to group the data into smaller
sections. PostgreSQL then isolates these sections and sorts only the
remaining unsorted columns within each group.
[ amount: UNSORTED ] [ amount: SORTED ]
category (pre-sorted) | amount category (pre-sorted) | amount
----------------------+------- ----------------------+-------
A | 30 ===> A | 20
A | 20 ===> A | 30
A | 50 ===> A | 50
B | 30 ===> B | 15
B | 15 ===> B | 30
C | 5 ===> C | 5
C | 25 ===> C | 10
C | 10 ===> C | 25
EXPLAIN SELECT * FROM sales ORDER BY category, amount;
QUERY PLAN
---------------------------------------------------------------------------------------------
Incremental Sort (cost=0.33..6851.99 rows=100000 width=12)
Sort Key: category, amount
Presorted Key: category
-> Index Scan using sales_category_idx on sales (cost=0.29..2351.99 rows=100000 width=12)
Postgres can cache some of its results for future operations within session
memory rather than shared memory (Shared Buffers) to
improve performance.
Memoize
#
Optimizes Nested Loop joins by caching inner scan
results. When the outer side of the join contains duplicate keys (for
example,
1, 1, 1, 2), a standard nested loop would redundantly
rescan the inner relation for the value 1 multiple
times.
A Memoize node eliminates this redundancy by acting as
a local cache. If there is a cache hit, it can then refer to cache for
the matching rows rather than rescanning the inner node for them in that
loop.
EXPLAIN (ANALYZE, COSTS FALSE)
SELECT tab1.id FROM tab1
JOIN tab2 ON tab2.id = tab1.id;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Nested Loop (actual time=0.028..5.184 rows=9045 loops=1)
-> Seq Scan on tab1 (actual time=0.011..0.743 rows=10000 loops=1)
-> Memoize (actual time=0.000..0.000 rows=1 loops=10000)
Cache Key: tab1.id
Cache Mode: logical
Hits: 9994 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Only Scan using tab2_id_idx on tab2 (actual time=0.003..0.003 rows=1 loops=6)
Index Cond: (id = tab1.id)
Heap Fetches: 0
In the above example, the Seq Scan compared its
entries to the values from the Index Only Scan.
However, instead of performing a full comparison every time, it would
check the cache from the Memoize node. In 9,994
situations, it was able to determine that the comparison had already been
made by a duplicate value in an earlier iteration of the loop and
received its matches from the cache. In 6 cases, the cache check missed
and the full comparison had to be made.
Materialize
#
PostgreSQL may need to receive the results of a node multiple times to
complete an operation. The amount of times a node is executed is denoted
by its loops field, which is returned in an
EXPLAIN ANALYZE operation. For example, the node below
was re-executed 4 times.
-> Seq Scan on tab1 (actual time=0.001..0.095 rows=100 loops=4)
Instead of rerunning the same node, PostgreSQL can cache the results for
the duration of the query in session memory or within a temporary file.
This operation is denoted by the
Materialize node.
EXPLAIN (ANALYZE, COSTS FALSE) SELECT tab1.id FROM tab1
JOIN tab2 ON tab2.id = tab1.id;
QUERY PLAN
--------------------------------------------------------------------------
Nested Loop (actual time=0.023..1.444 rows=100 loops=1)
Join Filter: (tab1.id = tab2.id)
Rows Removed by Join Filter: 9900
-> Seq Scan on tab1 (actual time=0.011..0.019 rows=100 loops=1)
-> Materialize (actual time=0.000..0.007 rows=100 loops=100)
-> Seq Scan on tab2 (actual time=0.006..0.013 rows=100 loops=1)
In the example, the Seq Scan was run only once (
loops=1). Its return set was cached as shown by the
Materialize node, which was then rereferenced 100
times (loops=100) by the node above it
Nested Loop.
InitPlan
#
An InitPlan is not a planner node, but it is notable
enough that it is worth mentioning as a standalone. It indicates that
PostgreSQL executed a subquery
or portion of a CTE once, cached its
result, and reused it whenever parent nodes needed it again.
EXPLAIN ANALYZE
SELECT * FROM tab1
WHERE tab1.id = (SELECT id FROM tab2 WHERE tab2.id = 10 LIMIT 1);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on tab1 (cost=4.30..5.43 rows=1 width=4) (actual time=0.035..0.036 rows=1.00 loops=1)
Filter: (id = (InitPlan 1).col1)
Rows Removed by Filter: 9
Buffers: shared hit=5
InitPlan 1
-> Limit (cost=0.29..4.30 rows=1 width=4) (actual time=0.014..0.014 rows=1.00 loops=1)
Buffers: shared hit=4
-> Index Only Scan using tab2_id_idx on tab2 (cost=0.29..4.30 rows=1 width=4) (actual time=0.013..0.013 rows=1.00 loops=1)
Index Cond: (id = 10)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=4
These are operations that do not fit into a specific category.
LockRows
#
Emerges when a row lock is explicitly requested in a query. Appears when
a locking clause, such as
FOR SHARE is present in a SELECT
query.
SELECT * FROM tab1 FOR KEY SHARE;
QUERY PLAN
---------------------------------------------------------------
LockRows (cost=0.00..123.00 rows=5000 width=12)
-> Seq Scan on tab1 (cost=0.00..73.00 rows=5000 width=12)
Limit
#Allows a child node to escape early once it has returned a certain number of values.
EXPLAIN SELECT * FROM tab1 LIMIT 1;
QUERY PLAN
--------------------------------------------------------------
Limit (cost=0.00..0.01 rows=1 width=4)
-> Seq Scan on tab1 (cost=0.00..35.50 rows=2550 width=4)
Group
#
May be used when the GROUP BY clause is present
without an aggregate function. It filters out duplicates with matching
GROUP BY keys.
It requires the group keys to be pre-sorted, either through an index that already provides the ordering or through an explicit sort step.
EXPLAIN SELECT rank FROM tab1 GROUP BY category, rank;
QUERY PLAN
--------------------------------------------------------------------
Group (cost=380.19..417.69 rows=5000 width=6)
Group Key: category, rank
-> Sort (cost=380.19..392.69 rows=5000 width=6)
Sort Key: category, rank
-> Seq Scan on tab1 (cost=0.00..73.00 rows=5000 width=6)
Unique
#
Often used in conjunction with
DISTINCT and
UNION commands, it removes duplicate rows from a scan.
It requires the group keys to be pre-sorted, either through an index that
already provides the ordering or through an explicit
sort step.
EXPLAIN SELECT DISTINCT rank, category FROM tab1;
QUERY PLAN
--------------------------------------------------------------------
Unique (cost=380.19..417.69 rows=5000 width=6)
-> Sort (cost=380.19..392.69 rows=5000 width=6)
Sort Key: rank, category
-> Seq Scan on tab1 (cost=0.00..73.00 rows=5000 width=6)