Most of the great operas with overtures have great overtures, but the character and function of the overture certainly changed over time. Baroque and classical overtures tended to be rather generic, with composers such as Handel and Rossini stealing from themselves and reusing their curtain-raisers for different operas. These pieces became more specific in character in the later eighteenth and nineteenth centuries, utilizing tunes from the opera and setting characteristic moods. Nobody did this better than Wagner, whose overtures and preludes (his preferred term) are among the greatest orchestral works of the Romantic era, but there are wonderfully dramatic overtures from Beethoven, Weber, Verdi, and others.
I'm having trouble thinking of a poor opera with a great overture, or the reverse. I suppose the overtures to many of the bel canto era operas wouldn't be terribly interesting if played alone. I love Norma, but would I listen to just the overture?