Be Inclusive: Welcome Non-Key Columns in B-Tree Indexes — Markus Winand

Discover the power of `include` clauses in Postgres indexes, a powerful tool for optimizing queries, and learn how to use it to speed up queries with index-only scans, while avoiding performance issues and write amplification.

Key takeaways

The include clause in Postgres indexes is mainly used for index-only scans, and it’s an important feature to leverage for efficiency.
The fewer indexes, the better, as they can cause performance issues and increase write amplification.
The include clause is ignored by everything except index-only scan, which makes it a powerful tool for optimizing queries.
includddd (short for include) doesn’t help the WHERE clause at all and is mainly used for filtering.
The include clause can’t be used with functions, expressions, or computed columns.
Indexes with include columns can be ordered for query optimization
Some cases where the include clause is useful:
- When retrieving a large amount of data from a primary key
- When the table has many columns
- When the query uses both the key and the include columns
Index-only scan can also be useful when retrieving data from a table, especially with dense data structures.
include columns can also be used for snapshot isolation levels, allowing each transaction to see a consistent view of the data.
Using include columns for sorting can be beneficial, especially when dealing with large amounts of data.
Checking the visibility map is important for transactional isolation and snapshot isolation.
The main benefit of the include clause is to speed up queries with index-only scans.
In general, the fewer indexes, the better, to avoid performance issues and write amplification.

Be Inclusive: Welcome Non-Key Columns in B-Tree Indexes — Markus Winand

More talks