Indexes in Postgres
PostgreSQL: Documentation: 16: Chapter 11. Indexes
Types
B-tree indexes
B-tree indexes are binary trees that are used to sort data efficiently. They're the default if you use the INDEX command. Most of the time, a B-tree index suffices. As you scale, inconsistencies can be a larger problem, so use the amcheck extension periodically.
Hash Indexes
Hash indexes store a 32-bit hash code derived from the value of the indexed column. Hence, such indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the equal operator
PostgreSQL: Documentation: 16: 11.2. Index Types
B-tree vs Hash Indexes
A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <=, <, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.
Hash Index Characteristics
- They are used only for equality comparisons that use the = or
<=>operators (but are very fast). They are not used for comparison operators such as<that find a range of values. Systems that rely on this type of single-value lookup are known as "key-value stores"; to use MySQL for such applications, use hash indexes wherever possible. - The optimizer cannot use a hash index to speed up
ORDER BYoperations. (This type of index cannot be used to search for the next entry in order.) - MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a
MyISAMorInnoDBtable to a hash-indexedMEMORYtable. - Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)
8.3.9 Comparison of B-Tree and Hash Indexes
BRIN indexes
A Block Range INdex (BRIN) can be used when your table is naturally already sorted by a column, and you need to sort by that column. For example, for a log table that was written sequentially, setting a BRIN index on the timestamp column lets the server know that the data is already sorted.
BRIN works in terms of block ranges (or “page ranges”). A block range is a group of pages that are physically adjacent in the table; for each block range, some summary info is stored by the index. For example, a table storing a store's sale orders might have a date column on which each order was placed, and most of the time the entries for earlier orders will appear earlier in the table as well; a table storing a ZIP code column might have all codes for a city grouped together naturally.
PostgreSQL: Documentation: 16: 71.1. Introduction
Bloom filter index
A bloom index is perfect for multi-column queries on big tables where you only need to test for equality. It uses a special mathematical structure called a bloom filter that's based on probability and uses significantly less space.
GIN and GiST indexes
Use a GIN or GiST index for efficient indexes based on composite values like text, arrays, and JSON.
There are two kinds of indexes that can be used to speed up full text searches. Note that indexes are not mandatory for full text searching, but in cases where a column is searched on a regular basis, an index is usually desirable.
-- Creates a GiST (Generalized Search Tree)-based index. The column can be of tsvector or tsquery type.
CREATE INDEX name ON table USING gist(column);
-- Creates a GIN (Generalized Inverted Index)-based index. The column must be of tsvector type.
CREATE INDEX name ON table USING gin(column);
A GiST index is lossy, meaning that the index may produce false matches, and it is necessary to check the actual table row to eliminate such false matches.
GIN indexes are not lossy for standard queries, but their performance depends logarithmically on the number of unique words.
Performance
- GIN index lookups are about three times faster than GiST
- GIN indexes take about three times longer to build than GiST
- GIN indexes are moderately slower to update than GiST indexes, but about 10 times slower if fast-update support was disabled.
- GIN indexes are two-to-three times larger than GiST indexes
https://habr.com/en/company/postgrespro/blog/448746
PostgreSQL: Documentation: 9.1: GiST and GIN Index Types
SP-GiST
SP-GiST indexes, like GiST indexes, offer an infrastructure that supports various kinds of searches. SP-GiST permits implementation of a wide range of different non-balanced disk-based data structures, such as quadtrees, k-d trees, and radix trees (tries).
PostgreSQL: Documentation: 16: 11.2. Index Types
Partial Indexes
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate.