How to set up the required index? What to look for in EXPLAIN? How to understand that the index is not used?

To set up the necessary index, you should:

1. Determine which columns are frequently used for filters, sorting, or joins.
2. Create an index on these columns, considering the order and type of index (e.g., B-tree).

When analyzing a query with EXPLAIN, look for:

• Whether an index is used (in the "key" field or "Index Scan").
• Which specific index is chosen.
• The number of rows planned to be read (rows).
• The type of access (e.g., "index scan", "seq scan" — sequential scan).

If EXPLAIN shows "seq scan" (sequential scan) instead of index access, it means the index is not used.

Reasons for not using an index:

• The query does not match the index structure (e.g., filter on a non-indexed column).
• Outdated statistics — update the table statistics.
• The optimizer considers sequential scan faster (e.g., with large data volumes).

Example of creating an index in PostgreSQL:

CREATE INDEX idx_users_email ON users(email);

Checking the plan:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

If the output shows Index Scan using idx_users_email, the index is used.