It is normal for the fan to run at high speed (6000 rpm) at power-on and boot-up for my mid-2007 MacBook 2,1 for a few moments. Not sure about more recent models.
Once booted and logged in, then it should 'throttle back' to about 2000 rpm unless you have lots of (40 or 50%) CPU utilization.
Over the years, dust can build up inside the case and drastically reduce cooling efficiency as well. There was also a thread around these forums about the thermal paste connecting the CPU and the heatsinks drying out over time, reducing cooling efficiency even further, if you're adventurous and want to go on a cleaning expedition. A blast of compressed air at the vents might be enough to either convince you that A) further internal cleaning is needed, or B) that it doesn't seem to be a problem.
I used my son's MacBook Pro mid-2009 for a few days before I realized it even HAD a fan. Newer Intel Core2Duo processor on a smaller die using fewer watts and a more efficient video chipset combine to produce far less heat to begin with, and the aluminum case dissipates heat far more effectively!
If you recently installed (reinstalled, restored) OS X and you have hundreds of thousands of files, the automatic file indexing for Spotlight can take a while - hours at least, and maybe days. It can be disabled, but I let it run to completion, because I like the search capability. There are also OS X 'maintenance' processes that run automatically at like 3:00 a.m. if you leave your computer on overnight. If they don't run then, they'll try to run when you next start up, and that could be slowing things/causing more CPU utilization. Time Machine backups can also cause my fan to come on. If Time Machine is trying to do a backup almost immediately at start-up, that could also be the issue.
I start the Activity Monitor app running in my system Dock at boot up, with the System Memory pie chart active. I've noticed that whenever the 'green' slice gets tiny (less than 100MB free RAM), the fan runs at higher speed, and beachballs are much more likely.
My system is limited to 3GB usable system RAM. If you're still running with 2GB, an upgrade to 4GB is worthwhile. And if you're running out of free hard disk space (10 to 15GB FREE is a bare minimum needed for reliable performance), a larger 7200rpm hard drive (especially if your current drive is 5400 rpm) can speed things up.
The current release of Safari 5.1.7 (6534.57.2) on Snow Leopard 10.6.8 seems to be a memory hog or has a serious memory leak over time. Safari can be the only app running, with only one tab open and can eventually cause free RAM to plummet. Flash-heavy pages seem to speed up the process.
There are some Mac apps, free and paid that let you monitor and/or 'control' the fan. Monitoring it is fine, but I don't recommend trying to change the fan behavior. If the CPU is getting hot, it needs to be cooled!