If you index a field that increases or decreases sequentially between documents in a collection, like a timestamp, then the maximum write rate to the collection is 500 writes per second. If you don't query based on the field with sequential values, you can exempt the field from indexing to bypass this limit.
When does Firestore care if a field is changing sequentially versus simply changing?
A timestamp is still sequential, it's just not consecutive. Same applies to alphabetical, and pretty much any reasonably predictably generated values.
Sequential data in the indexes is likely to be written in close physical proximity on the storage media too, which is the reason it causes a limit on write throughput.
Concerning the why part of your question, the Firestore documentation provides a great explanation:
Cloud Firestore places lexicographically close index entries on the same tablet. If the index values in a tablet are too close together, such as for timestamp fields, Cloud Firestore cannot efficiently split the tablet into smaller tablets. This creates a hot spot where a single tablet receives too much traffic, and read and write operations to the hot spot become slower.
