I've had a fairly positive adoption-from-foster care experience. I had wanted to "straight adopt" (not foster) but that didnt pan out after over a year of waiting (i was pretty open as to age/race/emotional needs etc and was hoping to be placed with a school aged boy), so i switched agencies and got a foster license. I've had four foster children in the 3+ years that i fostered. Three of those i adopted. Only one was placed with the purpose of adoption, the others had reunification as their initial goal. My first placement was three weeks old, and i finalized his adoption before he was 11 months old. I had a little girl just under a year old, she went to an aunt after two months and it was pretty clear from placement that she would not be staying. Then my third placement was 16.5 months old, i thought he might go home to his dad but within a month of placement the goal was officially changed to adoption even though they still continued with reunification efforts, and TPR took place about 8 months after placement. After TPR i had his older half-sister placed with me, and i knew i'd adopt her as TPR had already occurred. She was 8 years old at placement.
My boys are completely healthy with no special needs, no drug exposure, nothing. Totally typical kids. My daughter is physically very healthy, but does have learning disabilities (that i suspect may be related to possible prenatal exposure to alcohol, but no proof), ADHD that is fairly well controlled with meds, and alot of attachment issue-related behaviors that drive me up a wall. But for the most part she is well adjusted and none of the "horror story" issues you hear about sometimes with older kids (no acting out, she's safe with other kids, pets, property etc)...my state doesnt really have a "Fost/Adopt" track like some do, so basically you have to depend on your foster worker funneling potentially adoptable kids your way. So if your state DOES have something a little more official, i'd say your chances of getting placed with a young child who is ultimately TPR'd is quite good.
The only caution i'd give, which im sure you already know and have given thought to, is that until you have the final decree of adoption in hand that states that child is legally and forever yours, NOTHING is guaranteed and ANYTHING can happen. Its MUCH harder than you think it will be to live with that uncertainty even in a "good" situation. With my first placement, TPR happened at not quite four months old, the only possible interested relative decided at six months old he couldnt be a resource, there was no visits, no one wanted the baby, it was totally clearly an adoptive placement at that point...and yet it wasnt until i got that finalization in the mail did i realize i'd been essentially holding my breath for close to a year. And the same with the other kids, once it was over and they were TRULY mine it was such a relief. Because with foster kids they can leave at any time. Even with my daughter, no one was supposed to want her and then just before she was to move in with me a cousin staked a claim (then disappeared.) So you just never know.