Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →In PostgreSQL, a B-tree is the general-purpose default index: it supports equality and range searches and can return rows in order. A hash index is a narrower option for equality comparisons. A covering index is not a separate index method; it is an index that contains all the columns a query needs, potentially allowing an index-only scan. These terms and behaviors below refer specifically to PostgreSQL 18, since index names and capabilities differ among database engines.
What a database index does
An index is an auxiliary structure that helps PostgreSQL locate rows without scanning the entire table for every query. The index method determines which kinds of conditions PostgreSQL can use it for. As the PostgreSQL 17 documentation puts it, “Each index type uses a different algorithm that is best suited to different types of indexable clauses.” PostgreSQL 17: Index Types
As an Amazon Associate I earn from qualifying purchases.
Choosing an index is therefore about matching its capabilities to a query’s filters, ordering, and returned columns—not choosing whichever method sounds fastest.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsWhat is the difference between a B-tree and a hash index?
| Feature | B-tree | Hash |
|---|---|---|
| Default in PostgreSQL | Yes. CREATE INDEX uses B-tree unless another method is specified. |
No; it must be selected explicitly. |
| Predicate support | Equality and range comparisons, including operators such as =, <, and >=. |
Simple equality comparisons using =. |
| Ordered retrieval | Can provide rows in index order. | Does not provide the same ordered retrieval capability. |
| Stored representation | Organizes indexed values for its supported comparisons and ordering. | Stores a 32-bit hash code derived from the indexed value. |
PostgreSQL’s documented capabilities are described in PostgreSQL 18: Chapter 11. Indexes and PostgreSQL 17: Index Types. The latter documents the 32-bit hash code and equality-only use.
#1 Best Overall
B-tree: the broad default
PostgreSQL creates a B-tree when you write CREATE INDEX without naming a method. Use it as the starting point for ordinary indexed comparisons: it supports equality and range predicates, including conditions such as BETWEEN and IN, and can help satisfy an ORDER BY when the requested ordering matches the index.
A B-tree may also help with a pattern such as LIKE 'foo%', subject to the relevant collation and operator-class conditions. This does not imply that it can efficiently handle a pattern with a leading wildcard, such as LIKE '%bar'. See the documented qualifications in PostgreSQL 17: Index Types.
Rank #2
Hash: equality only
PostgreSQL hash indexes are considered for simple = comparisons. They store a 32-bit hash code derived from the indexed value, rather than providing B-tree’s range and ordered-retrieval behavior. That makes hash a specialized equality-oriented option, not a general replacement for the default B-tree.
The official documentation establishes which clauses these methods support, not a universal speed ranking. Whether either index helps a particular query depends on the workload and whether PostgreSQL chooses to use it.
What is a covering index?
“Covering” describes the relationship between an index and a query: the index contains the columns the query needs. It is not a separate PostgreSQL index method. A common design puts the search column in the key list and a returned-but-not-searched column in INCLUDE:
CREATE INDEX tab_x_y ON tab (x) INCLUDE (y);For a query such as SELECT y FROM tab WHERE x = 'key';, the index contains both the search key x and the selected value y. PostgreSQL can potentially use those entries to answer the query without retrieving the table row.
Included columns are payload, not search keys. PostgreSQL does not use y to qualify the index search, and an included column does not become part of a unique index’s uniqueness test. PostgreSQL 18 documents included columns for B-tree, GiST, and SP-GiST indexes. See PostgreSQL 18: CREATE INDEX.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →When can a covering index avoid visiting the table?
An index-only scan is possible only when the index method supports it and all columns needed for the query are available from the index. Even then, “index-only” does not guarantee that PostgreSQL can avoid the table entirely.
PostgreSQL stores row-visibility information for multi-version concurrency control (MVCC) in the visibility map, not in index entries. When a relevant heap page is not marked all-visible, PostgreSQL must visit the heap row to check whether it is visible to the query. As a result, table update patterns and visibility-map state affect whether a covering index actually reduces heap access. The mechanics are detailed in PostgreSQL 18: Index-Only Scans and Covering Indexes.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How to choose among them
- Choose B-tree as the starting point when the workload needs equality or range comparisons, ordered retrieval, or a combination of these.
- Consider hash only for equality-oriented lookups where B-tree’s range and ordering features are not needed. The documented capabilities alone do not show that hash will be faster for a given workload.
- Consider a covering design for a frequent query when the index can hold its search keys and returned columns, and an index-only scan could help. Use key columns for filtering and
INCLUDEfor additional output columns that need not be searched. - Account for table churn when judging a covering design: if visibility-map state frequently requires heap visits, the expected reduction in table access may not materialize.
- Account for index width and writes before adding payload columns. Wider indexes use more space and can slow searches; carrying redundant data also increases index maintenance costs.
Costs of adding included columns
An included column duplicates table data in the index. PostgreSQL warns that adding non-key columns indiscriminately increases index size and may slow searches. There is also a hard limit: an insert can fail if an index tuple exceeds the type’s maximum size. These trade-offs are covered in PostgreSQL 18: CREATE INDEX. A covering index is worthwhile only when its workload-specific benefit outweighs its storage and write costs.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Recommended Free Tools
