SFX used to mean special effects, and I suppose in some cases it still does. Though most people (as oakstreet illustrates) take it to mean SOUND effects.
With the advent and outrageous growth in popularity of computer generated special effects, the VFX abbreviation is more common these days. SFX could however still refer to special effects, as in the type done mechanically, on set. But in post it's all special optical effects and special visual effects (hence the VFX abbreviation noted above).
the abbreviation that bugs me is CGI.. it makes me think of web servers and web programming, CGI = Common Gateway Interface, which is a method of handling dynamic data to and from the web server. I personally refer to all computer generated images (or the film version of CGI) as CG, so it makes sense in my head. In that sense, CG can be Computer Generated, or Computer Graphics... which, as I recall is what they USED to call it, before they started throwing that I in there mucking it all up.
As for your original question... I suppose it all depends on what you intend to do. Are you looking for compositing software, or software to generate elements (3d, etc)?
As oakstreet mentioned, Blender3D and Lightwave are great for 3d, as well as a handful of others -- Blender has a good price though (FREE).
There is decent selection of compositing software available, like the FXHome stuff you mentioned (which looks impressive by the way). The biggest three are probably: Adobe has
After Effects, Autodesk has
Combustion (as well as Smoke, Flame and Flint, all of which used to be developed by DISCREET before autodesk bought them out), and eyeon software's Digital Fusion (or DF) which is one that a lot of VFX guys seem to be hoping will become the most widely used.. couldn't find much other info on it that their few videos of reasons to use it,
here
Hope that helps a little anyway.