There's tons of optimizations for postgresql for things like complex subselects, unions, aggregates and what not that provide better performance overall for complex operations.
I tend to think of MySQL as a good general purpose text / info storage system, and Postgresql as a genuine relational database.
I.e. they occupy different solution spaces.
However, they are both trying to take on each other's attributes. Postgresql is getting better at the general purpose high speed storage thing, while MySQL adds relational features.
For instance, on a table with 1,000,000 rows in postgresql, I now get this kind of performance:
explain analyze select * from accounts where aid=546320;
QUERY PLAN
Index Scan using accounts_pkey on accounts (cost=0.00..3.08 rows=1 width=100) (actual time=0.09..0.09 rows=1 loops=1)
Index Cond: (aid = 546320)
Total runtime: 0.22 msec
220 uSeconds is in the range of speed I expect of MySQL, not Postgresql, and yet, as of 7.4 Postgresql is pretty much a match for MySQL on simple selects.
