What types of indexes do you know?

• B-tree
• Hash
• GiST
• SP-GiST
• GIN
• BRIN

The B-tree is the most versatile and is used by default. Hash is suitable only for simple equality (=). GiST, SP-GiST, GIN, and BRIN are specialized for different data types and queries (e.g., geographic data, full-text search, arrays).

-- Creating a B-tree index (default)
CREATE INDEX users_email_idx ON users (email);

-- Creating a Hash index
CREATE INDEX users_name_hash_idx ON users USING HASH (name);

-- Creating a GIN index for full-text search
CREATE INDEX documents_content_gin_idx ON documents USING GIN (to_tsvector('russian', content));

The choice of index type depends on the nature of the data and the frequency of certain operations. Using the correct index type is critical for optimizing query performance.