Having said so, Postgresql has actually surprised me very little throughout time. The planner's "reasoning" is quite comprehensible. Inevitably I trigger a slow plan here and there but, once identified, correction is usually straightforward. My overall opinion is that postgresql has quite solid theoretical foundations and it shows.
What I mean is that, in advanced stuff, there'll always be some "magic", and that postgresql has the least amount of black box magic I've ever seen on a RDBMS (I'm looking at you, Oracle).
(Postgresql has been my non-clustered database of choice since about 2004. I'm definitely biased.)
In my experience, 9/10 production use cases are covered using a path as primary key with prefix matching (select path,data from tree where path='a/b' or path like 'a/b/%') - as a side effect your subtree nodes will often be on the same table page (mySQL by default, pgSQL when using CLUSTER).
The advanced features allow you to have more than one way to do things, sometimes in a slightly more concise way (I've been tempting to use jsonb type in place of simple many-to-many tables if one of the values are just always be string). It's not that I'm expecting my app to be web scale or anything, but I'd rather not having to redesign everything one a query choke on a million records. And most of the docs tend to be light on performance characteristics.
As performance is one of the most important aspect in database system in general (which I'd say on par with consistency, and way a head of features), it'd be nice to have docs focus more on not just how to use features, but when it shouldn't be used.