Re: suggestion for optimizing with non-matching compound indexes

[Harrison Fisk]

Hi,

This does exist in other databases (at least Oracle that I know of, not sure about others), and is called index skip scans. Read more at http://www.praetoriate.com/oracle_tips_skip_scan.htm for example.

The issue with doing a skip index scan is that the usefulness is directly dependant on the cardinality of the front column. Most of the time the most selective column is the first one, so this doesn't work out very well in many cases.

Also a skip scan is always going to be slower than a regular index lookup. In your example, why wouldn't you add another index just on the b column? Even if a skip index scan was possible, it will be more efficient to add an index on b.

With that said, there are, of course, possible cases where this could be useful. I do believe this is on the TODO list, however I don't believe it is near the top.