To start, I'm assuming you're talking about low-cost index funds tracking the S&P500. All of the "actively managed" funds tracking an index are, IMO, farces designed to extract money for the fund managers rather than delivering value to the (index fund) share holders. A passively-managed index fund is a fairly boring (and cheap) operation to manage, primarily buying and selling shares to keep the same proportions as the tracked index, be it the popular S&P500, the CRSP Total US Market index, or any other imaginable index. The low-cost appears in the very low expense ratio, some measured in single-digit hundreds of 1 percent (eg 0.04% for VTSAX).

As for whether an index fund tracking American large-cap stocks is a "sure fire" investment, absolutely not. Any investment needs to be viewed in terms of its appropriateness, such as being properly diversified (within one's abilities) and the timescale must match one's financial objectives. The conventional adage is that everyone would like to win the lottery, but when pressed for a more specific answer, most would say that they just want to live without worrying about finding an income. That is to say, they're just looking for "enough".

Practical financial advice aims to sustainably achieve "enough", usually framed in terms of retirement but quite frankly, the process works for all sorts of goals, such as saving for higher education for oneself or a child, buying a car, building a marriage dowry, or planning to support aging parents. What's distinct with these scenarios are: the amount needed, and the time remaining to achieve that amount.

For a mid-20s newly-employed knowledge worker (eg mechanical engineer), they have about 40 years until retirement age. Time is a very valuable asset, because time can overcome short-term problems like economic recessions or high interest rates. Even if a recession strikes just prior to turning 65, the nest egg will have grown with 40 years of dividends prior to the recession taking a small haircut. Alternatively, starting one's career in a recession means post-recovery investments will bolster the savings.

The large-cap index funds (like S&P500) are high risk, high reward. For someone with a long time horizon and a good savings rate like a young professional, large-cap makes a lot of sense. But having only large-cap would be wholly inappropriate for a retired octogenarian who just needs to draw a steady income to pay their living expenses. After all, having already gotten so far in life, the meaning of "enough" changed from "high growth of nest egg" to "drawing down the nest". So this retired person would probably have gradually swapped out most their index funds for things like bonds, which pay less in dividends but are steady even through recessions and bad times. But they might still keep a small portion in large-cap, in case they live longer than expected.

For a longer discussion about investing according to one's definition of "enough", I would recommend reading some pages from the Bogleheads community, like this one: https://www.bogleheads.org/wiki/Bogleheads%C2%AE_investment_philosophy