I’ve been working with Microsoft SQL Server since the late 1990s, and about a decade ago, I started working with Postgres. I just recently started teaching Postgres index tuning, and one thing comes up all the time.

I really wish I could tell Postgres which index to use for a query.

In Microsoft SQL Server, I can use index hints like this:

That tells SQL Server to use the ix_Location index when accessing the Users table. This feature is really useful when I’m comparing the performance between a few different indexes because I can create all the indexes I want, and then rapidly switch between different indexes for the same query – without having to drop & recreate different indexes.

With Postgres, I have to make sure that only the index I wanna demonstrate is in place at the time, otherwise Postgres may choose a different one. This makes storytelling harder, especially on real-world database sizes like the >100GB Stack Overflow demo database, because creating indexes on a >50GB table can take serious time. I hate making attendees wait for that stuff, so it leads to convoluted storytelling when I have to write the demos in a way that avoid real-time index creation.

At the same time, I feel guilty asking for an index hint feature because I really don’t like it when folks use index hints in production code. Index hints lead to several problems:

• Brittle database designs: when the DBA gains more skills (or a new DBA comes in), and they want to design a better index to satisfy more queries, the index hint is problematic. Microsoft SQL Server’s index hints are really commands, not hints: if the index name doesn’t exist, the query simply fails. (That’s a problem too, and I wish Microsoft would fix that.)
• Brittle performance: when a newer version of the database engine comes out, and it might use a different index more efficiently, the upgrade doesn’t magically improve performance until the index hint is removed from code.
• More work for developers: index hints are technical debt, so when the above two situations happen (index changes or version upgrades), developers have to touch & redeploy their code to benefit.
• Brittle ISV apps: heaven forbid independent software vendor developers learn about index hints. If they embed index hints in their code, and then their customer DBAs monkey around with the indexes, the ISV’s app can fail because the indexes were changed, leading to support problems and finger-pointing blame.

So in summary, I really wish Postgres had index hints – but only to make my training classes easier to build & deliver. I totally understand why the product doesn’t have it, because there’s a part of me that wishes Microsoft SQL Server didn’t have it, either.