If you’ve been around the block with window functions, you’ve probably heard of the POC indexing strategy: Partition by, Order by, Covering. In other words, with a query, focus on the columns in the
PARTITION BYclause (in order!), then the
ORDER BYclause (again, in order!), and finally other columns in the
SELECTclause to make the index covering (not in order! though it doesn’t hurt!).
But do read on to understand why this is not sufficient.