Why the index made it slower

So we added an index to make this query faster. It got four hundred times slower.

The mental model

Here's the one idea you need. The planner never actually reads your data. It reads a summary of it.

The mechanism

And that summary is a histogram. A few hundred buckets, standing in for a few hundred million rows.

Now, our data wasn't evenly spread. One customer had ninety four percent of the rows.

So the planner figured it'd touch about forty rows, picked the index, and then walked four and a half million.

Back to the anomaly

A sequential scan would've read that table once. The index read it four and a half million times. One row at a time.

Sources

• planner uses histogram statistics