What indexes are available in PostgreSQL?

PostgreSQL offers various index types that optimize data search and sorting:

• B-tree (default) — suitable for most comparison operations (=, <, <=, >, >=).
• Hash — efficient for equality operations but less versatile.
• GIN (Generalized Inverted Index) — used for indexing arrays, JSON, full-text search.
• GiST (Generalized Search Tree) — supports complex data structures, such as geometric objects.
• SP-GiST — specialized GiST for certain data types.
• BRIN (Block Range Index) — effective for very large tables with ordered data.

Example of creating a B-tree index:

CREATE INDEX idx_users_lastname ON users(lastname);

The choice of index type depends on the data type and query nature.