Index and Table Access
- Seq Scan
Seq Scanoperation scans the entire relation (table) as stored on disk (like
TABLE ACCESS FULL).
- Index Scan
Index Scanperforms 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 SCANfollowed by a
TABLE ACCESS BY INDEX ROWIDoperation. See also Chapter 1, “Anatomy of an. 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)
Index Only Scanperforms 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. ”
- Bitmap Index Scan / Bitmap Heap Scan / Recheck Cond
Tom Lane's post to the PostgreSQL performance mailing list is very clear and concise.
Index Scanfetches 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
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
Hashin 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
Sortoperation needs large amounts of memory to materialize the intermediate result (not pipelined). See also “Indexing Order By”.
Aggregates a presorted set according to the
group byclause. This operation does not buffer large amounts of data (pipelined). See also “Indexing Group By”.
Uses a temporary hash table to group records. The
HashAggregateoperation 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”.
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
Indicates the use of window functions. See also “Using Window Functions for . Pagination”
PostgreSQL cannot execute pipelined top-N queries when using window functions.