Optimization tales with CockroachDB: the slow list of users (part 4)

This article is part 4 of a series on optimizing CockroachDB queries. The author investigates a query that lists users (identities) with a maximum latency of 10.4 seconds, despite using the correct index and returning only a few rows. The query uses SELECT DISTINCT, which is unnecessary for the 'thin' API mode where no joins occur, but was likely added for the 'full' mode with joins. The author explains the investigation process, identifies DISTINCT as the likely culprit for the thin case, and discusses the complexity of removing it due to convoluted code. The article focuses on SQL optimization, database performance, and debugging techniques relevant to IT/technology professionals.