I mostly use plugins to spice up my digital 3d renderings, to add special effects, like explosions, or to tweak the looks, or add weathering, dirt etc to textures I make for my 3D models.
Here's some of my faves and what they can do :)

DSB FLUX

This is an old one, it has several effects I like for "SFX" and weird stuff, like "Electrosphere" which creates oil-on-water-rainbow-like fills.
http://www.photoshop-filters.com/html/dsb_flux.htm
http://www.jumbo-psp.com/Tutorials/Lesjes/Tools/Filters/dsbflux.zip

INDIA INK from Flaming Pear

This is the plugin I use to make most of my cartoon images, give a sort of "wood-cut", or cartoon-like look
http://www.flamingpear.com/indiaink.html

SOLAR CELL, LUNAR CELL and GLITERRATO from Flaming Pear

These plugins respectively create suns, planets (and moons), and starry space with nebulae.
Awesome combo for creating space scenes! Lunar Cell can also be used to create textures for 3d models of planets, so I love that one ;)
http://www.flamingpear.com/glitterato.html
http://www.flamingpear.com/solarcell.html
http://www.flamingpear.com/lunarcell.html

SUPERBLADEPRO by Flaming Pear

This plugin adds texturing, usually metallic, to selections, letting you create gorgeous metal text and many other effects.
http://www.flamingpear.com/blade.html

KNOLL LIGHT FACTORY

I can't afford this one at moment but have enjoyed playing with the demo. It's awesome for creating lens flares, suns, light bursts, and similar things, such as SFX of space engines, weapons fire etc.
Made by the guy who is largely responsible for Photoshop's creation and a snot load of SFX in films, John Knoll
http://www.digitalanarchy.com/index.html

ALIEN SKIN

Alienskin have a lot of plugins, some of them are really nice. From metal plate effects, to fire, smoke, glass (makes great web buttons) etc.
-Xenofex has nice lighting and staining effects (weathering and dirtying 3d textures is vital for realism)
-Impact has the glass, metal, rust, chrome solar coronas, dripping, scales, weaves, wood and other effects.
http://www.alienskin.com/

ULEAD GIF-X 2

This is one of my all time faves!! it has superb lightning, best I'vve ever seen in a plugin :), as you can choose it's start and end point, as well as all other variables, and is animatable!
-Also has variety of presets for animations like flag waving etc.
-Lot of different light effects.
http://www.ulead.com/gifx/runme.htm

DEPTH OF FIELD PRO by Richard Rosenman

Depth of field, is the effect you get when something is in or out of focus, based on distance; so, a finger up close is sharp, background is blurred. Great way to make shots look like they have real depth.
Richard Rosenman's plugin lets you adjust that, but what's great about it, is that it can use, easily, "depth masks" made by 3d art programs, including animations, to add in post work, depth of field, very accurately.
I really like this plugin :)
http://www.dofpro.com/

UNIVERSE by Diard Software

This plugin, or stand alone app, can create many astronomical features, like star fields, nebulae, galaxies etc. It's pretty neat.
http://www.diardsoftware.com/

GENESIS by Futurefantastic

Alas this is no longer sold, bought it years ago. It creates amazing light effects, like sci-fi stuff...you'd have to see it to get why it's so cool!! :)
This is a test image I made with it a long time ago.
http://www.silverblades-suitcase.com/artofadreamer/htm/genesis.htm

PARTICLE ILLUSION by Wondertouch

Ok, this is a cheat, lol, it's not a plugin for Photoshop, but you can use it to add effects to 2d images, so you can export an image from Photoshop, give it some special effects, and bring it back into Photoshop.
Particle Illusion does all kinds of jaw-dropping "particle" effects, from fire, laser blasts, "Matrix" style numbers falling down the screen explosion...absolutely gorgeous stuff!
So you can take a boring pic and add lots of effects to it.
http://www.wondertouch.com/
http://www.wondertouch.com/movies/space_small.mpg

I did the fireball effects in this pic, using Particle Illusion

MACHINE WASH by Mister Retro

This is a suite of different plugins for adding dirt, wear and alterations. I find it perfect for adding grime and weathering to textures for 3d models.
http://www.misterretro.com/image_filters.html

PHOTOSHOP ACTIONS

Ok, another cheat, not a true plugin ;) Photoshop can use "actions", scripts that run a sequence of effects. This can produce a really cool variety of end results, touching up, or altering images. I like ones that give subtle, softening and enhancements, or cartooning.
This is one suite of actions from one creator, others exist, but this I use this one :)
http://market.renderosity.com/mod/bcs/index.php?ViewProduct=48600
This image shows one picture altered in several different way by these actions, this pic is the artist/sellers' work and advertisement.