Debugging a slow password reset query in CockroachDB: from 700k rows to 10
Summary
A software engineer recounts investigating a slow password reset query flagged by their CTO. The query was reading 700k rows instead of the expected ~10 rows. What initially seemed like a simple missing-index fix turned into a deeper investigation involving CockroachDB's query optimizer quirks, index selection issues, and the complexities of database query planning. The engineer walks through the debugging process, the root cause analysis, and the open-sourced fix applied to resolve the performance issue.
Source
bskyDebugging a slow password reset query in CockroachDB: from 700k rows to 10gaultier.github.ioKey quotes
· 3 pulledThis query should not read 700k rows, but like 10 or something.
The plan looks ok IMHO, indices are being used: [copy pasted production plan]
And spoiler alert, he was dead right.
You might also wanna read
Introducing Row-Level Security for Fine-Grained Access Control in CockroachDB
The article discusses the importance of fine-grained access control in modern data infrastructure and introduces Row-Level Security in Cockr
SQL Performance Optimization: Methods for Identifying Slow Queries
This technical article provides a practical guide on how to identify slow SQL queries that need optimization for performance improvements. I
ohdear.app·9mo ago
PostgreSQL VACUUM Limitations: Why Indexes Become Bloated and How to Fix Them
This article debunks common misconceptions about PostgreSQL's VACUUM command, explaining that while VACUUM cleans dead tuples from tables, i
boringsql.com·6mo ago
SQLite Query Optimization: Using Uncorrelated Subqueries to Skip Correlated Subqueries for Performance Gains
The article describes a SQLite query optimization technique where an uncorrelated scalar subquery is used to skip expensive correlated subqu
emschwartz.me·6mo ago
Haydex: Engineering Journey to 178.6 Billion Rows Per Second Database Performance
This article tells the engineering story of Haydex, a database company that transformed a failed filter prototype into a production system c
axiom.co·9mo ago
The performance cost of random UUID primary keys in SQLite and clustered indexes
This article examines the performance drawbacks of using random UUIDs (specifically UUID4) as primary keys in SQLite databases. It explains
andersmurphy.com·29d ago
Comments
Sign in to join the conversation.
No comments yet. Be the first.