If you have a high-end Pioneer player with a decent comb-filter, with the exeception of the HLD-X9, S-Video should really provide the best picture but it will depend on what you're plugging it into.
If you're plugging straight into a modern TV, use composite as the comb-filter in the TV should be far superior to the one in the player.
For the best results you need to start investing in extra tech to run the picture through such as a Darbee DVP-5000S and / or DVDO VP50/ VP50Pro - these really will make a world of difference. You won't get blu-ray quality but with the right disc you can match standard DVD imo.
BTW, discs do matter when it comes to LD picture quality. Shit in = shit out.
If the LD was mastered off of VHS, or just badly mastered, you could run your player through every VP known to man and the picture would still be gash.
Generally THX mastered discs / Criterion discs / most post '97 stuff will offer a good picture.