Why match explanations timed out on large accounts

When you ask Hireo why a candidate scored the way they did, or ask for a pipeline summary by job title, the answer depends on first finding every job that matches that title in your account. For most accounts this takes no time. For one of our largest it ran into a time limit, and the match explanation failed.

What we expected

The lookup reads job leads in pages. We assumed each page costs about the same, so the total grows in a straight line with the size of the account. The answer was always correct. A user who got a result got the right one. The problem was that on large accounts there was sometimes no result.

What broke

The ticket described a match explanation that timed out for a large account. Our first suspicion was the one the ticket suggested, a limit on how long the signed-in role may run a query. We checked and corrected that: the tool does not run as the signed-in user, but it still gets the same eight-second budget, because of how the database proxy switches roles.

The real cause was in how pages were fetched. The query asked for rows "from position N", filtered by organization and by a title containing the search text. A title search that begins with a wildcard cannot use an index, so every page started again from the beginning of the organization and counted forward to N. Page 50 cost fifty times what page 1 cost. Reading the whole set cost roughly the square of its size.

What we tried and what worked

We measured on the production database, read-only, using the query planner's own timing. We picked the worst title we could find, not a typical one. Page one took about the same before and after, around 34 milliseconds. But deep into the result, a page that took close to 4 seconds now took a few milliseconds. Summed across all pages, answering one request had needed roughly 210 seconds of database time against an 8 second budget. It now finishes in under a second.

The change was small. Instead of "give me rows from position N", the query says "give me rows after the last job id I saw". The table already had an index on that id, and the query was already sorted by it, so each page starts exactly where the previous one ended and does not count again from zero. Three tools shared that one lookup (the pipeline summary by title, the list of candidates attached to a job by title, and the match explanation), so all three were fixed together.

What you get now

Match explanations and title-based pipeline summaries do not slow down as your account grows, because the cost of a page no longer depends on how deep it is. If you have an account with a lot of history and ran into a timeout on these, it should be gone.

Written by the Hireo team.

Built by BetterQA. Hireo is built by BetterQA, a software testing company in Cluj-Napoca. BetterQA is ISO 27001 certified, a NATO NCIA agreement holder, and rated 4.9 on Clutch.