It's the opportunity cost. I think religion is mediocre in this game because most of the bonuses are thoughtlessly ported from civ5's religious system. +1 culture from pastures what a pretty good pantheon in civ5 because it was a fairy significant marginal gain in culture output per city for a large part of the game even if the average city only had 1 or 2 pasture tiles to work. In this game we get 0.7 culture per citizen, it's nothing special. We're forced to take a worship building belief in this game, and the buildings aren't that good and you need to dedicate a precious district slot to even build one. I could build a holy site and a shrine and a temple and a whatever it's called for a bunch of faith and 2 science and some GP points that go into the trash because I only get one, or a campus and a library and a university and get way more science and a housing and GS points that will actually contribute to generating a great person.
Don't look at it as GPP points going to waste, you generate faith, which are universal GPP points.
I definitely agree founding a religion is not worth it unless you really intend to aggressively spread it and go for a religious victory though. It's set up like that, all the founder beliefs scale with how many cities the religion is present in. This ultimately means you have to compete with those who are going for a religious victory, which defaults to you needing to go for one or waste your investment at higher difficulties.
The follower and worship beliefs are what people pursuing other victories will be interested in. You don't have to found a religion to pick these, just wait for the religion you want to get spread to one of your cities then spread it yourself from there -being careful not to give someone else a victory. You'll occasionally have games where the one you want doesn't find its way to you, but this is the case with founding religions as well unless you play china and rush Stonehenge.
Overall, I really like Holy sites, I think people over-value campus and culture districts. They're nice, but focusing on either too hard I find yields diminishing returns. Faith is very versatile. I think a balance of faith and science districts, or faith and culture districts, is better then twice as much science districts or twice as much culture districts.
Also I don't see the problem with the pantheons being weak, they're really only meant as an early game boost to take advantage of whatever area your capital starts in (with a few exceptions). 1 culture for haunting moos quadruples the culture for new cities and thus the border expansion rate, which is the difference between borders growing and borders never growing until much food is acquired. Do you buy monuments? Okay then, don't and this is basically 200 gold per city. It also helps for hitting that early political philosophy which is a pretty big early game threshold to hit for many reasons, the obvious policy slots and government bonuses to the less obvious diplomatic bonuses and therefore trade opportunities.