Operations

Index and Table Access

Seq Scan

The Seq Scan operation scans the entire relation (table) as stored on disk (like TABLE ACCESS FULL).

Index Scan

The Index Scan performs a B-tree traversal, walks through the leaf nodes to find all matching entries, and fetches the corresponding table data. It is like an INDEX RANGE SCAN followed by a TABLE ACCESS BY INDEX ROWID operation. See also Chapter 1, “Anatomy of an SQL Index”.

The so-called index filter predicates often cause performance problems for an Index Scan. The next section explains how to identify them.

Index Only Scan (since PostgreSQL 9.2)

The Index Only Scan performs a B-tree traversal and walks through the leaf nodes to find all matching entries. There is no table access needed because the index has all columns to satisfy the query (exception: MVCC visibility information). See also “Index-Only Scan: Avoiding Table Access”.

Bitmap Index Scan / Bitmap Heap Scan / Recheck Cond

Tom Lane’s post to the PostgreSQL performance mailing list is very clear and concise.

A plain Index Scan fetches one tuple-pointer at a time from the index, and immediately visits that tuple in the table. A bitmap scan fetches all the tuple-pointers from the index in one go, sorts them using an in-memory “bitmap” data structure, and then visits the table tuples in physical tuple-location order.

— Tom Lane

Join Operations

Generally join operations process only two tables at a time. In case a query has more joins, they are executed sequentially: first two tables, then the intermediate result with the next table. In the context of joins, the term “table” could therefore also mean “intermediate result”.

Nested Loops

Joins two tables by fetching the result from one table and querying the other table for each row from the first. See also “Nested Loops”.

Hash Join / Hash

The hash join loads the candidate records from one side of the join into a hash table (marked with Hash in the plan) which is then probed for each record from the other side of the join. See also “Hash Join”.

Merge Join

The (sort) merge join combines two sorted lists like a zipper. Both sides of the join must be presorted. See also “Sort Merge”.

Sorting and Grouping

Sort / Sort Key

Sorts the set on the columns mentioned in Sort Key. The Sort operation needs large amounts of memory to materialize the intermediate result (not pipelined). See also “Indexing Order By”.

GroupAggregate

Aggregates a presorted set according to the group by clause. This operation does not buffer large amounts of data (pipelined). See also “Indexing Group By”.

HashAggregate

Uses a temporary hash table to group records. The HashAggregate operation does not require a presorted data set, instead it uses large amounts of memory to materialize the intermediate result (not pipelined). The output is not ordered in any meaningful way. See also “Indexing Group By”.

Top-N Queries

Limit

Aborts the underlying operations when the desired number of rows has been fetched. See also “Querying Top-N Rows”.

The efficiency of the top-N query depends on the execution mode of the underlying operations. It is very inefficient when aborting non-pipelined operations such as Sort.

WindowAgg

Indicates the use of window functions. See also “Using Window Functions for Efficient Pagination”.

Caution

PostgreSQL cannot use conditions on the result of window functions to abort top-N queries.