I struggle daily with Finder's search. It cannot find files in my Downloads folder even if I type the exact name. Do I need to rebuild some index or some other setting?

It couldn't hurt. I don't have problems like that.

mdutil -E -a

That command will erase and rebuild Spotlight index on all volumes.

Since Tahoe, Spotlight has multiple modes. You might have better luck forcing it into folder & files mode. That's command-2. So command-space, spotlight UI appears, keep holding command key, press 2. There's also 1 for applications only, 3 for "actions" and 4 for clipboard history.

The annoying thing with Finder is when you're in a folder, you start to search, and then it defaults to "This Mac" and not the god damn folder you're in.

Bloody daft.

That's because you have that set in your Finder Settings.

With the Finder open, go to Settings > Advanced, and change the drop-down at the bottom to "Search the Current Folder". This is something I do on the first run of every new Mac I set up.

No, that's because it's a default value, so the complain is 100% spot on. Bad defaults should be treated as a bug.

"Search this Mac" should be confined to Spotlight OR as a fallback if the local folder doesn't return anything.