This article explores a fascinating optimization in the KingbaseES database kernel, where adding a LIMIT 1 clause can make the entire DISTINCT operation redundant. The author demonstrates with a concrete SQL example and explains the internal logic: when the query only needs one row, the database can stop early after finding the first distinct value, avoiding a full sort or hash aggregation. This is a clever optimization that leverages the semantics of LIMIT to short-circuit expensive operations. While the example is specific to KingbaseES (a Chinese database based on PostgreSQL), the principle applies to other databases that support similar optimizations. The article is short but insightful, offering a practical tip for SQL tuning. However, it assumes familiarity with query execution plans and database internals.
A surprising database optimization where LIMIT 1 eliminates the need for DISTINCT, explained with KingbaseES internals.