It does seemingly obvious things, such as offering known host names when using ssh or sftp (taken from config and known_hosts files in ~/.ssh) or offering the valid command options that follow the cvs command (e.g. commit, update, log, etc.). Here's a big list of what commands it will expand arguments for.
I think people who do more systems administration than I do will probably benefit more than I will, but at least I can now reduce how many times cvs complains about the invalid command "cvs udpate".
According to its page at freshmeat.net this has been around since 2001. As distributed for cygwin, the file is now nearly 9000 lines long plus another 1700 lines in an associated subdirectory (bash_completion.d). This seems a tad monolithic. I think it'd make sense for these completion functions to be split up and packaged along with the executable itself. This is an extension of what is currently done. Regardless of the installation packaging system (rpm or apt on linux, fink on mac, cygwin for cygwin), the package already contains not just the executable, but also configuration files, manual pages, other documentation, and often custom shell settings to be placed in /etc/profile.d -- perhaps these completions should just be added into the files placed in profile.d since the contents are just bash commands which add information to the current session.