There are "rockstar" or "olympic" professions, where the reward for being among the best in the world is large, and the reward for being not among the best is really bad. These professions are a trap.
This doesn't mean there aren't clever mice who get the food from the trap and don't get trapped. But most of the mice who try to eat from a mouse trap don't get the reward they are expecting.
The most visible members of the profession by far are the "rockstars", those who defeated the trap. So when you look into working towards that profession, you end up aiming for the "rockstars" as the goal.
However, until you put a huge amount of effort into getting better at the profession, you cannot know if you actually are (or will be) a rockstar or not. You don't, and cannot, have the information required to know if you are going to be a rockstar; for every rockstar, there are 10,000s or more people who are convinced they have a chance. Determining if you are someone who has a real reason to know they will be a rockstar, or someone who falsely believes they have this reason, is not something you can test without a lot of effort.
Now, you can look at what your past peers in a similar situation discovered after putting in that effort. These peers are people with similar qualifications and similar confidence that they can become rockstars in the profession given their place along the path to stardom.
As an example, suppose you qualify for "Americas Got Talent", and your goal is to be a pop superstar. You can count how many people have qualified for that show in the past, and how many of them became pop superstars, and use that as a decent prediction of your chances of doing it. Probability isn't destiny; but this provides a realistic, tested model of your chances.
So when aiming to join a for a profession, what you need to look at
The median member of the profession's status, rewards, etc., and
The winnowing rate from people starting at your point to being a member of the profession.
For academia, you are someone who has "won" each stage of what could be a path towards becoming a professor multiple times. You probably won at elementary, high school, college, graduate school "winnowing" stages, doing exceptionally well compared to your peers. But the same is true of almost all of your peers, people getting a PhD. In that class, you aren't special; it is best to assume you are typical as a starting point.
You have now gotten to yet another winnowing stage; where some small fraction of people with PhDs get a tenure track position; and only a small fraction of tenure track positions result in becoming a "rockstar" professor. If the odds of that is lower than 1000:1, knowing nothing about you other than you have a PhD, that even if you continue to work towards becoming a "rockstar" professor that you'll almost certainly not become one.
If your goal is to become a "rockstar" professor, you almost certainly won't. Do you want to become a median professor with tenure? Reaching that point is going to be challenging, but it is at least a plausible outcome. Now, what is a median professor? It is a professor at a small, liberal arts college, nowhere near a major city, spending most of their time teaching introductory classes to disinterested students. Plus some time for research, a living wage, and some upper level classes with enthusiastic (but not exceptional) students; professors often are educated at some of the best universities in the country, but only a very small number teach there.
For your alternatives you should also look at both the winnowing rates and the median result of passing each winnowing stage.
The rewards here could be financial, social status, or whatever.
If you would be satisfied with the median results of a plausible path (with, say, at least a 5% chance of success), that path is something you should consider. You should still look at fallback plans (what happens in the other 95% of the time), but aiming for that 5% is an achievable goal.
If the chance of success is under 5%, broaden the "success" criteria until it breaks a 5% threshold, then look at the median rewards in that 5% success zone. If that doesn't count as "winning" to you, find another path, or find a way to up your chances above 5% (but don't fool yourself!)
This means you shouldn't get a PhD unless the process of getting a PhD (getting years to do some research while being paid to feed yourself) is enough. You shouldn't train for the Olympics unless living the life of an athlete is reward enough. And you shouldn't go on to do an academic career after your PhD unless being a professor at a mediocre school doing median amounts of research in a small under funded lab is enough.
Becoming a rockstar, even after insane amounts of talent, sweat and tears, is winning the lottery. Almost nobody at a stage when they aren't a rockstar knows if they'll get there, because for everyone who is convinced they can do it, 100 are falsely convinced, and those that believe it is beyond them are going to be only marginally less likely to succeed.