Can GROUP BY still be optimized after all these years?

One of the things I like the most about the world of databases is how a simple operation can become extremely complex when it's optimized to its highest levels.

As part of that line of thought - I thought it might be interesting and fun to share a novel optimization we recently found that improved Pivot's performance significantly on GROUP BY queries that find the top K groups by aggregate value. For example, "which 10 items have the most orders?":

SELECT item_id, COUNT(*) AS c
FROM orders
GROUP BY item_id
ORDER BY c DESC
LIMIT 10;