Bolt - 95 cents

knowing where to put the bolt - 95 bucks per hour.

I've had many similar experiences with Postgres. I always seem to be the one adding the query logging to the apps. Then I feed the prod queries into the query planner. Then run the results through this tool: http://tatiyants.com/pev/#/plans/new Tweak the queries, add/remove indexes. Voilà.

The developers didn't actually know where the problem is because they didn't root cause it. They knew it lay within a certain pretty large bounding box (a particular query), but didn't drill into it further, beyond checking that indices are used (eliminating some inadvertent unindexed search as being the root cause).

If you actually know where (or each one of the multiple wheres if several places collude), that is usually very close to knowing why; often the same.

They should have had the intuition that if a query takes 20 seconds, even in a testing scenario where the system is not bogged down, it must be churning through a lot of data all over the place. Then think: does the query actually need to be looking at a lot of data? Maybe it's wastefuly looking at more records than necessary. They didn't imagine what the machine might have to do to satisfy the query, just accepting it as a black box that the DB has optimized as well as it can be (so just throw hardware at it).

Sounds like ops vs dev (or DBA) right there; it does make sense to a point that ops would grow into that kind of behaviour, given that they can't really allocate resources into performance enhancements for software (especially if it's from an external party or off-the-shelf). To them it's a black box.

But if it's an in-house thing then there should be open communication lines. The SRE paradigm makes sense there, where I see a SRE as part dev, part ops. They can identify perf issues as a software problem and either fix it or send it back to the authors.

It was more like dev & dev.

I am not criticizing anyone because I was just lucky to notice the query could be rewritten. But it was also the fact that I had a little more knowledge about how the db engine handles queries.

So we all looked in the right direction, we all found the bottleneck but the solution was different based on different knowledge.

I would go so far as to say, their "solution" was not a solution at all. Granted, in the absence of being able to recognize alternatives, it may have been the best available option left, but it fundamentally missed the root cause of the query slowness.

Your experience exactly shows why having a diversity of opinions/backgrounds/expertise on a team is a very valuable trait. Had no one realized you could rewrite the query, would scaling vertically and upgrading Oracle been a fatal mistake for the team? Probably not, but damn if it wouldn't have been a big waste of time/money.