Programming Tutorials

  You have identified a slow API endpoint. You enabled the slow query log, isolated the bottleneck SQL statement, and realized a crucial   WHERE   clause was filtering on an unindexed column. You confidently deployed a migration to add a B-Tree index. You ran the migration, verified the schema, and triggered the query again. The result? The query is still slow. When you run  EXPLAIN ANALYZE , you see the dreaded words:  Seq Scan  (PostgreSQL),  Table Scan  (SQL Server), or  ALL  (MySQL). The optimizer is completely ignoring your new index. This is not a bug in the database engine. It is a mathematical decision made by the Cost-Based Optimizer (CBO). This article explores exactly why databases ignore indexes and provides the rigorous technical fixes required to resolve it. The Cost-Based Optimizer: It's Just Math To fix the problem, you must understand the "Why." The Query Optimizer’s job is not to use indexes; its job is to retrieve data...