Need suggestion on below indexes, whether our composite index is enough and can the normal index be removed.

Scenario - We have ix4 and ix6 indexes as below, please review and advise if ix4 is required (as ix6 also includes serialnumber) and are there any expected performance penalties in case we remove ix4 due to column order or by any other factors


As pointed out by ypercube, Oracle (since v9) has supported using partial index scans in some circumstances where the relevant predicates do not include the first column in the index.

I'll leave the rest of the answer as it is relevant to anyone passing by who is using another DB. Further notes: from reading around I get the impression that the query planner will be eager to multiple seeks on another index where possible even if this is an option, so you may need to use an index hint if you explicitly want an index skip scan to happen. There is talk of such an operator being introduced to postgres in the not too distant future (see https://commitfest.postgresql.org/19/1741/ amongst other references).

Query planners can generally only use a composite index if they need to reference the first column in the index, so an index on columns a, b, c could be useful for queries needing to filter/sort/both by a, a & b or a & b & c. It may also be used when referencing other columns too, of course, for example if filtering by a and d but there is no composite index to help that more directly.

They generally can't use a composite index to filter/group/sort by columns later in the list unless the earlier ones are also needed. It is conceivable that they could use columns later in the index, but maintaining the statistics needed to make detecting when this is a beneficial operation would be expensive in the general case, making it an impractical optimisation to implement, so I doubt any query engine out there does this sort of thing.

tl;dr: if you have queries that filter/sort/group by serialnumber but not modelnumber, your smaller index is likely to be needed despite serialnumber being part of the composite index.

  • Oracle has a "Index Skip Scan" feature that can actually do what you describe, e.g. use an index on (a,b) for a query with WHERE b = 5 (if there is not a better index and if the optimizer decides it is better than a table scan or whatever), but in general, yeah, +1. – ypercubeᵀᴹ Dec 24 '18 at 14:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.