A 2700x Speedup from One Index, and Three Ways to Still Get It Wrong
Slow queries are the most common production performance killer, and they rarely announce themselves in logs. A developer who can read an execution plan and spot a Seq Scan on a large table can fix a 1000x slowdown in minutes instead of watching CPU sit at 90% while the business screams.
A 2-million-row transaction log table without indexes forces a parallel sequential scan that burns CPU and returns a single-row lookup in 88 milliseconds—roughly 1000x slower than acceptable for OLTP. Adding a B-Tree index on the lookup column collapses execution time to 0.032 ms, a 2700x improvement, by swapping the scan for a direct index seek.
Three common patterns still defeat the index. Wrapping the indexed column in an expression like `log_id + 1` triggers a full scan because the optimizer cannot match the transformed value against the index tree. Implicit type conversion is more nuanced: KingbaseES will safely cast a string constant to match an integer column, but reversing the types—passing an integer against a varchar column—forces a cast on the column side and kills the index. When a query retrieves nearly all rows, the cost-based optimizer deliberately ignores the index and chooses a sequential scan, since the random I/O of index lookups plus row fetches costs more than a straight disk sweep.
Most junior developers misread 88 ms as fast because it is below human perception, but OLTP single-row lookups should be measured in microseconds; a 1000x gap is a concurrency bomb.
Index creation is not a one-time fix. The optimizer's decision to use or ignore an index depends on the query shape, data distribution, and type-matching rules that change with every release of the database engine.
The implicit-cast behavior is engine-specific and version-dependent. A rule that was true for PostgreSQL 9 may not hold for KingbaseES, so testing with `EXPLAIN` on the actual version is the only reliable answer.
High-selectivity queries that bypass indexes are not a bug; they are the optimizer correctly trading random I/O for sequential I/O. The real fix is partitioning or columnar storage, not more indexes.
Performance tuning shifts from reactive firefighting to proactive review when DBAs inspect execution plans during code review, catching full scans before they reach production.