Earlier quoted context omitted.
I'd like to see your full setup for your claim about 10x slower, because I suspect you're doing something wrong.
Sure, here's the pastebin of the postgres one: http://pastebin.com/Phc8KnYU I thought I was doing something wrong, so I asked on SO: http://stackoverflow.com/questions/32755348/group-by-count-q... This is a very simple example, but I always run into major performance issues with Postgres. Some of them are my fault, from not knowing that array aggregation is a slow operation. MySQL advantages for me are that there is…
I'm going to ignore your baseless (and incorrect) assertion about "an inferior version" of UPSERT (MySQL's doesn't work properly except for the primary key). Your problem is that you are not doing an index only scan. The reason you are not doing an index only scan is, most likely, that the visibility bit is not set on the table. This is usually as a result of excessive writing to the table, but sometimes it is due to incorrectly tuned planner settings. Try setting your cpu_tuple_cost higher and/or cpu_index_tuple_cost lower. I have had to do this in the past to get index only scans to work. BTW, a quick way to force the issue is to SET enable_seq_scan TO off in psql.
I would add, you should really upgrade to 9.4, as there were some performance improvements to aggregates.