Adjusting pH seems to make a significant impact depending on what your base is. A buddy of mine brewed a pale ale with our philly tap water and it was terrible. I measured the final beer pH and it was ridiculously high - added some lactic acid and it improved a bit.
I'm sure some others can get a lot more scientific than I can but target mash pH seems to vary depending on what you're doing. I initially heard 5.2 referenced a lot (and it seems to work well for a few APAs I brewed). The Germans seem to like it a little higher - I've read from 5.3 to 5.5 for light beers and 5.4 to 5.6 for dark. Decocting vs Step Mashing seems to affect this as well.
Besides affecting conversion pH in the boil is important to consider as it affects bitterness. Sounds like you want it low enough that the bitterness isn't harsh and protein coagulation is encouraged but high enough that you don't loose too much utilization. Some folks mash at a higher pH then acidify during the boil.
Cash out pH is important for yeast health (5.1 to 5.3 or so I've read).
And finally, the pH of the serving beer affects taste. Above 4.6 and some bacteria can be active - that pale ale I referenced earlier was like 4.8 and terrible. Ideally fermentation will bring it down enough but Jim recently adjusted his APA in the keg with some lactic (to 4.3 was it?) and was pretty happy with the results. Messing around in the glass with baking soda/lactic acid is a fun way to approximate the effect.
I've been messing around with pH in Stouts lately and it makes a huge impact. I brewed one beer that ended up at 3.85 (Similar to Guinness) and that acidity/twang was a major component of the flavor. Subsequent stouts at 4.2 or so we're much smoother. pH was HUGE for me in stout experimentation. I also measured the pH of a well regarded local breweries (Tired Hands) pale ale and was surprised at how low it was (3.9 IIRC?) - I think the slight acidity melds well with the whole fruit juice sort of thing they go for.
Steve Holle talked about this in his 2008 seminar German Brewing Techniques. Kai talks about pH alot as well (
http://braukaiser.com/wiki/index.php?title=The_Theory_of_Mashing#Protein_converting_enzymes), sounds like the Germans in general focus more on it.
I'd buy a relatively cheap meter and some calibration solution and jump in, see if it adds value to you. Who knows, your water may be close enough to ideal for what you brew. Mine is fine for stouts but terrible for pale beers.