Q:

Multi-column indexes and ORDER BY

The PostgreSQL documentation states that if we run a query ... ORDER BY x ASC, y DESC on a table with an index ... (x ASC, y ASC), the index cannot be used because the directions do not match.

Does this mean that this index is completely useless, or can the database engine use the index to order the x ASC part (and then manually sort the y DESC part)?
What if we run a query ... WHERE x = 999 ORDER BY y DESC, can this index be used?

A:

1.
No, the index cannot be used, like the manual suggests. You can verify by creating such an index on any table and then, for the test session only:
SET enable_seqscan = OFF;

Then:
EXPLAIN
SELECT * FROM tbl ORDER BY ORDER BY x, y DESC;

Now, if the index could be used in any way, it would be. But you'll still see a sequential scan.
There is a pending patch to implement "incremental sort", but it's not there, yet. See:

https://wiki.postgresql.org/wiki/Postgres_Professional_roadmap#Incremental_sort
https://www.postgresql.org/message-id/flat/CAPpHfds1waRZ%3DNOmueYq0sx1ZSCnt%2B5QJvizT8ndT2%3DetZEeAQ%40mail.gmail.com

Corner case exception: If an index-only scan is possible, the index might still be used if it's substantially smaller than the table. But rows have to be sorted from scratch.
Related:

Optimizing queries on a range of timestamps (two columns)

2.
Yes, the index fits perfectly.
(Even works if the index has y ASC. It can be scanned backwards. Only NULL placement is a drawback in this case.)
Of course, if x = 999 is a stable predicate (it's always 999 we are interested in) and more than a few rows have a different x, then a partial index would be even more efficient:
CREATE INDEX ON tbl (y DESC) WHERE x = 999;

db<>fiddle here

