Index and Table Access
- Seq Scan
The
Seq Scan
operation scans the entire relation (table) as stored on disk (likeTABLE 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 anINDEX RANGE SCAN
followed by aTABLE 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
On my Own Behalf
I make my living from training, other SQL related services and selling my book. Learn more at https://winand.at/.
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
. TheSort
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 execute pipelined top-N queries when using window functions.