Did a bunch of migrations against two Oracle databases once for different environments of the same app. Same table structure, same indices, exact same SQL for migrations, pretty similar data (e.g. tables with ~40k and ~50k records in each of the environments).

And yet, a SELECT query that worked great against one environment essentially made the query hang on the other, with lots of CPU usage. No locks to speak of. SELECT against individual tables working without issues, but whenever you had two particular tables within JOIN, it all broke down.

Worked:

  SELECT ... FROM A
  WHERE ...;

Worked:

  SELECT ... FROM A
  JOIN B
  WHERE ...;

Worked:

  SELECT ... FROM A
  JOIN C
  WHERE ...;

Stalled:

  SELECT ... FROM A
  JOIN B
  JOIN C
  WHERE ...;

Tried re-creating the indices, did not help. Tried looking at the data, did not help. Killed all of the sessions and restarted the app, did not help. Inspected query plans, they were different - for the same query and the same indices. Made the DB re-generate the table statistics, that solved everything. It's unsettling that the process didn't happen in the background after the migration and that issues like this can eventually surface.

There are some things that you just cannot easily demystify, because they don't make a lot of sense, given that everything had worked properly for years previously without this kind of an issue. Just know that depending on the RDBMS that you use, theory will be different from what you'll see in practice once you venture past the very basics.

On a similar note, Oracle lets you generate indices automatically if you so choose, something that i discovered some time later and had hundreds of missing indices added for noticeable performance gains. Now, i hate the Oracle implementation of it (you cannot manually delete those indices, and the entire set of functionality is behind a paywall, much like the DB itself), but personally i think that dynamic index optimization might as well be something that should be present in PostgreSQL and MySQL/MariaDB as well.

Perhaps not to the point of creating new indices, but just telling you that you might want to add certain ones: "If you add new index X by executing the code Y, then the performance should increase by Z% based on the analysis in the background that have been conducted over the past W months with Q queries analyzed."