Построить оптимальный индекс для SELECT * FROM employee WHERE sex = 'm' AND salary > 300000 AND age = 20 ORDER BY created_at

For the query:

SELECT * FROM employee WHERE sex = 'm' AND salary > 300000 AND age = 20 ORDER BY created_at

the optimal index should consider both filtering conditions and sorting.

Recommendation:

• Create a composite index on columns (sex, age, salary, created_at).

Explanations:

• sex = 'm' and age = 20 are exact matches, so they should be placed at the beginning of the index.
• salary > 300000 is a range condition, placed after the exact matches.
• ORDER BY created_at to avoid additional sorting, include created_at in the index.

Example of creating an index in PostgreSQL:

CREATE INDEX idx_employee_sex_age_salary_created_at ON employee (sex, age, salary, created_at);

This index will efficiently filter by sex and age, then by the salary range, and directly return results sorted by created_at without additional sorting.