Optimization tales with CockroachDB: the slow list of messages (part 3)

This article is part 3 of a series on optimizing CockroachDB queries. It investigates a pagination query that takes 12 seconds and reads 1.8 million rows despite a LIMIT 100 clause. The author examines the query plan, index usage, and multi-region fan-out behavior to identify the root cause of the performance issue. The article walks through the investigation process, presents both right and wrong fixes, and concludes with results. It is a technical deep dive into SQL optimization, indexing strategies, and query performance tuning in CockroachDB, relevant to developers and database engineers working on performance-critical applications.