I'm not sure that there's a simple answer here. One place where people often run in to trouble is that they deploy cockroach with a global topology and then are surprised when things are much, much slower than in postgres. I'm going to assume that you're only talking about a single-region deployment.

One place cockroach radically differs from postgres is that it stores historical versions of rows and it stores them sequentially. This means that if you have an update-heavy workload and you do not lower your GC TTL for that data, you can see dramatic performance degradation that you would not see with postgres (see [1]).

I suspect that there are some cases where our optimizer will not make the same or as good of choices as postgres, especially in cases of joins across large numbers of tables. Our optimization of CTEs is relatively primitive compared to PG IIRC.

Another place where we do quite poorly that doesn't come up in production but commonly comes up in testing are schema changes. Cockroach schema changes are online and do not block concurrent writes. That being said, they require coordination and phases. Running schema changes in tight loops will yield relatively low throughput and performance deteriorates given some of the coordination is a function of the total number of schema elements. This almost always comes up in the context of testing things like ORMs where schema elements are created and destroyed in every test. I doubt this is the sort of thing you're looking for but it's definitely a thing that comes up.

As noted in the hash-sharded index post, cockroach is not well suited to scale write traffic incident on a single key. In these cases the in-memory, local synchronization of postgres is likely going to be more efficient. Sequential workloads suffer from a similar problem that doesn't exist in postgres. In fact, sequential workloads are quite well suited to their B+-Tree. That being said, this comes up relatively infrequently and when it does we have tools to mitigate it.

Our temp table implementation likely violates people's intuition. It primarily exists for compatibility to enable a broader suite of testing. Temp tables are as expensive as regular tables.

In general some of our data-intensive queries are going to be less efficient due partially the efficiency of our execution engine, partially to the efficiency of our KV, and partially due to the fact that the KV is implemented in a distributed way requiring a certain amount of data copying and synchronization whereas postgres can get by just passing pointers around in memory. I suspect its ability to achieve access locality for some workloads is immense and leads to wins. We have done a lot of work recently to "vectorize" our execution engine and make it more efficient (see [2]) and are in the process of replacing our storage engine, in some small part to eventually improve our scan speed (see [3]) and more generally improve our efficiency.

Maybe other can chime in but in my experience there isn't so much a general class of queries where we're orders of magnitude slower than postgres. Often when people see orders of magnitude differences it has to do with index selection in a plan for a specific query but this tends not to be a generalized problem and we're pretty active about rectifying these reported problems. We've also been focusing on adding tools to better understand query plans and performance.

More often it's the case that on a per-core basis, cockroach is less efficient in terms of throughput than a single instance of postgres but in return for that you get a number of benefits, one of which being that you can scale the thing with your load. If you're looking to optimize $/query in a high-throughput setting and don't care about the HA or other properties, cockroach probably isn't the answer. Often when you're trying to run a cost-optimized $/query system, it's because the scale is very large so postgres might not be the answer either. It may make a lot of sense if it's a very update-heavy workload where the total data set size is small.

Hope that was helpful. Maybe other who have had experience with cockroach and found it to be pathologically slower in some cases can chime in. We'd also love to hear about those cases in https://github.com/cockroachdb/cockroach/issues.

[1] https://github.com/cockroachdb/cockroach/issues/17229
[2] https://www.cockroachlabs.com/blog/how-we-built-a-vectorized...