by Markus Winand.


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

On my Own Behalf

I make my living from training, other SQL related services and selling my book. Learn more 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. The Sort operation 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 by clause. This operation does not buffer large amounts of data (pipelined). See also Indexing Group By.


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


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.


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


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

About the Author

Photo of Markus Winand

Markus Winand is the SQL Renaissance Ambassador. He is on a mission to introduce developers to the evolution of SQL in the 21st century. Markus can be hired as trainer, speaker and consultant via

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

The Use The Index, Luke! mug

Stickers, coasters, books and coffee mugs. All you need for learning.

Shop now

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license