Tell me about indexes: when you create them, when it is good, and when it is bad?

Indexes in databases are created to speed up data search and retrieval. They are usually created on columns that are frequently used in filters, sorting, or table joins.

When it is good to create indexes:

• Columns frequently used in WHERE, JOIN, ORDER BY clauses.
• Columns with high selectivity (many unique values).
• To accelerate aggregate functions and range searches.

When indexes can be detrimental:

• On columns with low selectivity (e.g., boolean fields).
• If the table is frequently updated, inserted into, or deleted from — indexes slow down these operations.
• Excessive indexes increase storage space and update load.

Example of creating an index in PostgreSQL:

CREATE INDEX idx_users_email ON users(email);

It is important to analyze the load and use EXPLAIN to evaluate the effectiveness of indexes.