I use the mental model of nested maps for "column order matters".
For example, an index "published, low_quality_probability, lang" is just a Map<date, Map<number, Map<lang, rowId>>> in my mental model.
These maps are ordered by the order the index possesses. That explains why column order matters and why one cannot skip columns and why it stops at range queries.
Just imagine getting a final rowId from these nested maps and you'll see why the index works for some queries and doesn't for others.
I use this same approach to explain indexes to database novices; it usually helps a lot, especially with how the leaf nodes / included indexes work as well (using that info instead of or in addition to rowid/primary key as the last value there)
It's actually a List<Tuple<date, number, rowid>>> in sorted order, and queries are more akin to binary search (they are not actually binary but use a wider fanout depending on many factors)
Yeah it might be closer to what's actually happening but it doesn't make it obvious why something doesn't work. The map model doesn't even cover non-unique indices. Or different type of indexes.
Just imagine getting a final rowId from these nested maps and you'll see why the index works for some queries and doesn't for others.