What are the main types of indexes in PostgreSQL?

In PostgreSQL, the main index types are:

• B-tree — the most common type, used for fast equality and range searches.
• Hash — an index for quick equality searches, but less versatile and used less frequently.
• GIN (Generalized Inverted Index) — effective for indexing arrays, JSONB, and full-text search.
• GiST (Generalized Search Tree) — supports complex data structures, such as geometric objects.
• SP-GiST — a specialized GiST for non-standard structures, such as prefix trees.
• BRIN (Block Range Index) — a compact index for large tables with ordered data.

Example of creating a B-tree index:

CREATE INDEX idx_users_name ON users USING btree(name);