We have several queries that filter by timestamp inequality and order by height. These queries have been observed to be extremely slow in practice, because the query planner uses the height index for the ORDER BY clause, and ends up doing a full scan to find the appropriate timestamp range.
This PR:
Orders such queries by timestamp, then height. This is logically equivalent, since timestamps are monotonic, but it allows the query planner to choose the timestamp index since it solves by the WHERE clause and ORDER BY clause. All that remains is a small incremental sort on height in case there are multiple blocks with the same timestamp. There are never more than a handful such blocks, so this sort is efficient.
We have several queries that filter by timestamp inequality and order by height. These queries have been observed to be extremely slow in practice, because the query planner uses the height index for the ORDER BY clause, and ends up doing a full scan to find the appropriate timestamp range.
This PR:
Orders such queries by timestamp, then height. This is logically equivalent, since timestamps are monotonic, but it allows the query planner to choose the timestamp index since it solves by the WHERE clause and ORDER BY clause. All that remains is a small incremental sort on height in case there are multiple blocks with the same timestamp. There are never more than a handful such blocks, so this sort is efficient.