I've got extensive experience with all of these, having used them for a few years each.
Modernizr
Includes HTML5shiv functionality
Also does a lot more – if you don't use the other features, then don't use it, it does slow down page loads, but is worth it if you need it!
HTML5shiv
Very small, just fixes html5 elements in IE, nothing else.
CSS3PIE
Lets you use border-radius, gradients and box shadow in older versions of IE. Also can allow PNGs in IE 6. Adds a noticeable delay to page load.
ie7.js (and ie9.js)
Gives you many CSS3 selectors, min and max width, multiple classes and fixed positioning. Also can have a png fix if you like. Doesn't seem to slow things down much.
Conclusion
My advice would fall into two categories:
If you are just using the new (is 2 years new on the internet?!) elements, and CSS3 selectors, then use ie9.js + the html5shiv. This is lightweight, and just lets you get on with things without having to remember that IE6 doesn't support anything.
If you are using a lot of CSS3 stuff, then CSS3PIE will sort out border-radius and box-shadow. The gradient support seems a little flaky, so I've always used a fallback image instead. Modernizr lets you easily deliver different properties to browsers with different support. I've mainly used this for determining whether a browser has CSS transitions and transforms, as they are useful for any image sliders or content carousels. It's worth using the customisation tool to only include the functionality that you want – the webforms stuff shows a textbox with 50 in it for a couple of milliseconds, so it's worth disabling if you don't want it.
Hope that's helpful!
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…