Our developers have come to us with a problem query in the Stack Overflow database that isn’t as fast as they’d like.

We have an index on DisplayName, but Postgres refuses to use that index in the execution plan, and it’s scanning the entire table instead:

Your Query Exercise challenge this week is simple: can you get that query to run dramatically faster, like under a second?

Post your solutions in the comments, and feel free to ask other folks questions about their solutions. If your solution involves code and you want it to look good, paste it as a Github Gist, and then include the Gist link in your comment.

The Query’s Backstory

Someone’s going to sneer, “Well the answer is clearly that you shouldn’t be querying by the length of a column.” Yes, but… in this situation, whenever a user pushes the GDPR right-to-be-forgotten button, the app is currently setting the Users.DisplayName column contents to be a UUID:

In the web site, the DisplayName length is limited to 35, so anyone with a UUID DisplayName (length 36) is someone that we need to process for the GDPR. Our right-to-be-forgotten process takes some time to run – we have to purge their data from several systems, like reporting – so we keep the user around while the app slowly works through those systems. We can’t just delete their row.

Yes, it would be nice to add a column like “UserStatus” or “ForgetMe” and have the app set that, but here in the real world, we don’t always get to redesign entire processes just because a query is slow. Try to get across the finish line quickly, as if you have 100 other things that are fighting for your attention too.

You know, like your day job. The one you’re avoiding right now. (Did I say that out loud?)