Spotlight worked best in OS X 10.4. It was near instantaneous on what is now considered ancient hardware. It's been steadily downhill from there.

Mostly because people still expect to use it to find "Recipe for Egg Salad.docx" without searching the entire Internet or executing some bizarre natural language query.