Keyword Overview

Table Access Modes

• Seq Scan

  Scans all rows of the table in sequence.

• Index Scan

  Scans indexes. The optimizer uses a two-step plan: the child plan node visits an index to find the locations of rows matching the index condition, and then the upper plan node actually fetches those rows from the table itself. Fetching rows separately is much more expensive than reading them sequentially, but because not all pages of the table have to be visited, this is still cheaper than a sequential scan. The upper-layer planning node sorts index-identified rows based on their physical locations before reading them. This minimizes the independent capturing overhead.

  If there are separate indexes on multiple columns referenced in WHERE, the optimizer might choose to use an AND or OR combination of the indexes. However, this requires the visiting of both indexes, so it is not necessarily a win compared to using just one index and treating the other condition as a filter.

  The following index scans featured with different sorting mechanisms are involved:

  • Bitmap index scan

    Fetches data pages using a bitmap.

  • Index scan using index_name

    Uses simple index search, which fetches data from an index table in the sequence of index keys. This mode is commonly used when only a small amount of data needs to be fetched from a large data table or when the ORDER BY condition is used to match the index sequence to reduce the sorting time.

  • Index-Only Scan

    Scans the index that contains all required data, instead of referencing a table.

• Bitmap Heap Scan

  Reads pages from bitmaps created by other operations and filters out the rows that do not meet the conditions. Bitmap heap scan can avoid random I/Os and accelerate read speed.

• TID Scan

  Scans a table by a tuple ID.

• Index Ctid Scan

  Scans a table based on the CTID index.

• CTE Scan

  Specifies that CTE evaluates subquery operations and stores query results as a temporary table. The temporary table is scanned by the CTE Scan operator.

• Foreign Scan

  Reads data from a remote data source.

• Function Scan

  Obtains result sets returned by functions and returns them as the rows read from tables.

• Sample Scan

  Queries and returns sampled data.

• Subquery Scan

  Reads subquery results.

• Values Scan

  Reads constants as part of the VALUES command.

• WorkTable Scan

  Scans a work table. Data is read in the middle of an operation which is usually a recursive operation declared using WITH RECURSIVE.

Table join modes

• Nested Loop

  A nested loop is used for queries that have a smaller dataset connected. In a nested loop join, the outer table drives the inner table and each row returned from the outer table should have a matching row in the inner table. The returned result set of all queries should be less than 10,000. The table that returns a smaller subset will work as an outer table, and indexes are recommended for connection columns of the inner table.

• (Sonic) Hash Join

  A hash join is used for large tables. The optimizer uses a hash join, in which rows of one table are entered into an in-memory hash table, after which the other table is scanned and the hash table is probed for matches to each row. Sonic and non-Sonic hash joins differ in their hash table structures, which do not affect the execution result set.

• Merge Join

  In most cases, the execution performance of a merge join is lower than that of a hash join. However, if the source data has been pre-sorted and no more sorting is required during the merge join, its performance excels.

Operators

• sort

  Sorts the result set.

• filter

  The EXPLAIN output shows the WHERE clause being applied as a filter condition attached to the Seq Scan plan node. This means that the plan node checks the condition for each row it scans, and returns only the ones that meet the condition. The estimated number of output rows has been reduced because of the WHERE clause. However, the scan will still have to visit all 10,000 rows, as a result, the cost is not decreased. It increases a bit (by 10,000 x cpu_operator_cost) to reflect the extra CPU time spent on checking the WHERE condition.

• LIMIT

  Limits the number of output execution results. If a LIMIT condition is added, not all rows are retrieved.

• Append

  Appends sub-operation results.

• Aggregate

  Aggregates the results generated from querying rows. It can be an aggregation of statements such as GROUP BY, UNION, and SELECT DISTINCT.

• BitmapAnd

  Specifies the AND operation of a bitmap, which is used to form a bitmap that matches more complex conditions.

• BitmapOr

  Specifies the OR operation of a bitmap, which is used to form a bitmap that matches more complex conditions.

• Gather

  Gathers data of parallel threads.

• Group

  Groups rows to perform the GROUP BY operation.

• GroupAggregate

  Aggregates the pre-sorted rows of the GROUP BY operation.

• Hash

  Hashes rows for the parent query. It is usually used to perform the JOIN operation.

• HashAggregate

  Aggregates the result rows of GROUP BY by using a hash table.

• Merge Append

  Merges subquery results in a way that preserves the sort order. It can be used to merge sorted rows in a table partition.

• ProjectSet

  Executes a function on the returned result set.

• Recursive Union

  Performs a union operation on all steps of a recursive function.

• SetOp

  Specifies a set operation, such as INTERSECT or EXCEPT.

• Unique

  Removes duplicates from an ordered result set.

• HashSetOp

  Specifies a strategy for set operations such as INTERSECT or EXCEPT. It uses Append to avoid pre-sorted input.

• LockRows

  Locks problematic rows to prevent other queries from writing, but allows reading.

• Materialize

  Stores subquery results in the memory so that the parent query can quickly access and obtain the subquery results.

• Result

  Returns a value without scanning.

• WindowAgg

  Specifies a window aggregate function, which is generally triggered by the OVER statement.

• Merge

  Performs a merge operation.

• StartWith Operator

  Specifies the hierarchical query operator, which is used to perform recursive query operations.

• Rownum

  Filters the row number in the query result. It usually appears in the ROWNUM clause.

• Index Cond

  Specifies the index scan conditions.

• Unpivot

  Specifies a transpose operator.

Partition pruning

• Iterations

  Specifies the number of iterations performed by the partition iteration operator on level-1 partitions. If PART is displayed, dynamic pruning is used.

  For example, Iterations: 4 indicates that the iteration operator needs to traverse four level-1 partitions. Iterations: PART indicates that the number of level-1 partitions to be traversed is determined by parameter conditions of the partition key.

• Selected Partitions

  Specifies the selected level-1 partitions for pruning. m..n indicates that partitions m to n are selected. Multiple consecutive partitions are separated by commas (,).

  For example, Selected Partitions: 2..4,7 indicates that partitions 2, 3, 4, and 7 are selected.

• Sub Iterations

  Specifies the number of iterations performed by the partition iteration operator on level-2 partitions. If PART is displayed, dynamic pruning is used.

  For example, Sub Iterations: 4 indicates that the iteration operator needs to traverse four level-2 partitions. Iterations: PART indicates that the number of level-2 partitions to be traversed is determined by parameter conditions of the partition key.

• Selected Subpartitions

  Specifies the selected level-2 partitions for pruning, which is in the format like: level-1 partition number:level-2 partition number.

  For example, Selected Subpartitions: 2:1 3:2 indicates that level-2 partition 1 of the second level-1 partition and level-2 partition 2 of the third level-1 partition are selected. Selected Subpartitions: ALL indicates that all level-2 partitions are selected.

Other keywords

• Partitioned

  Indicates operations on a specific partition.

• Partition Iterator

  Partition iterator, which usually indicates that a subquery is an operation on a partition.

• InitPlan

  Indicates a non-related subplan.