You thought it was a composite index, but doesn't the optimizer rearrange the columns in the correct order?

The database optimizer cannot always arbitrarily rearrange columns in a composite index. The order of columns in a composite index is critical because the index is built in this specific order, and its effectiveness depends on which columns and in what order they are used in queries.

If you create a composite index, for example, on columns (A, B), the index is effectively used for queries filtering by A or by A and B together, but not for queries filtering only by B. The optimizer will not rearrange the columns in the index to fit the query; it needs an index with the correct column order.

Therefore, it is important to create composite indexes considering the order of filtering and sorting in queries, rather than relying on the optimizer to rearrange columns.