Composite index and selectivity of its columns?

Question

Database

The composite index b of type is compared by tuples, that is, the order of index definition matters - (a,b) and (b,a) are not equivalent. Let's say there are two columns in some abstract table: x - foreign key, y - datetime, respectively, x has lower selectivity than y
I know and understand that it is better to define b index for columns with high selectivity, that is, the order will be as follows - ( y,x).
I'm right?

Answer 1

Composite index b of type is compared by tuples, that is, the order of index definition matters

Depends on the algorithm. True for btree, may be completely meaningless for other types. For example, if the DBMS builds a key for a hash index from the concatenation of the index fields - (a, b) will be no different from (b, a).
Doesn't matter.
The value is under which queries we make indexes. btree (a,b) cannot properly serve where b = value and a > value, for this query it is much more meaningful (b,a)
If you have a query where a = const and b = const, then btree indexes (a,b) and (b,a) will be equivalent.
If you have an index with an extremely selective timestamp in the first place - then what will you do with it? A strict time stamp comparison is extremely rare, and for any range request, the efficiency of subsequent fields falls catastrophically and most DBMS do not use them at all. Basically, they are used only if it is possible to take the index as a covering one.
Options if you try to do only btree(y,x) index:
- The DBMS will refuse to do FK at all. there is no suitable index for checking FK
- the DBMS will implicitly create this index when adding the FK
- the DBMS will allow the FK to exist without an index, but accordingly the fullscan of the entire table for each consistency check on this FK (possibly a separate scan for each row that caused the key integrity check)

Answer 2

In the general case - yes, such an index will work faster and rebuild faster.