Conscious guilt causes agony while unconscious guilt can shape a person.
I tend to see more people wracked with conscious guilt. Sometimes it's neurotic (ie, irrational in proportion), and sometimes it is good old ordinary guilt for rotten behavior and/or evil thoughts.
There are many causes of self-defeating or self-sabotaging behavior besides unconscious guilt (for examples, avoidance of difficult things or avoidance of risky challenges), but guilt is always on the list of considerations.
Dr. X discusses the topic here and here.