No, compilers do not do a bad job at all. The amount of optimization that can be squeezed out by using assembly is insignificant for most programs.
That amount depends on how you define 'modern C compiler'. A brand new compiler (For a chip that has just reached market) may have a large number of inefficiencies that will get ironed out over time. Just compile some simple programs (the string.h functions, for example), and analyze what each line of code does. You may be surprised at some of the wasteful things an untested C compiler does, and recognize the error with a simple read-through of the code. A mature, well-tested, thoroughly optimized compiler (Think x86) will do a great job of generating assembly, though a new one will still do a decent job.
In no case can C do a better job than assembly. You could just benchmark the two, and if your assembly was slower, compile with -S and submit the resulting assembly, and you're guaranteed a tie. C is compiled to assembly, which has a 1:1 correlation with the bytecode. The computer can't do anything that assembly can't do, assuming that the complete instruction set is published.
In some cases, C is not expressive enough to be fully optimized. A programmer may know something about the nature of the data that simply cannot be expressed in C in such a way that the compiler can take advantage of this knowledge. Certainly, C is expressive and close to the metal, and is very good for optimization, but complete optimization is not always possible.
A compiler can't define 'performance' like a human can. I understand that you said trivial programs, but even in the simplest (useful) algorithms, there will be a tradeoff between size and speed. The compiler can't do this at a more fine grained scale than the -Os/-O[1-3] flags, but a human can know what 'best' means in the context of the purpose of a program.
Some architecture-dependent assembly instructions can't be expressed in C. This is where ASM() statements come in. Sometimes, these are not for optimization at all, but simply because there is no way to express in C that this line must use, say, the atomic test-and-set operation, or that we want to issue an SVC interrupt with the encoded parameter X.
The above points notwithstanding, C is orders of magnitude more efficient to program in and to master. If performance is important, analysis of the assembly will be necessary, and optimizations will probably be found, but the tradeoff in developer time and effort is rarely worth the effort for complex programs on a PC. For very simple programs which must be as fast as absolutely possible (like an RTOS), or which have severe memory constraints (like an ATTiny with 1KB of Flash (non-writable) memory and 64Bytes of RAM), assembly may be the only way to go.