The classic example is a sequential scan, say for a COUNT(*) or SUM(foo). A single process would scan through all the blocks in order. A parallel scan would split the blocks into N buckets, scan them via N separate processes, and then combine the results.
Restricting every query to a single core places an artificial CPU cap on a system that would otherwise be IO capped.
Disks/storage is also getting faster (http://www.geek.com/chips/new-intel-storage-is-1000-times-fa...).
As analytics keeps gaining popularity, the need/desire to move more and more CPU bound processing to the data storage layer is increasing.
And finally, PostgreSQL's advanced query planning/partitioning support would immensely benefit from being able to split partition queries into multiple process buckets.
>The age-old assumption that I/O is slow and computation is fast is no longer true: this invalidates decades of design decisions that are deeply embedded in today’s systems
But this is just a start and there are lots more opportunities to improve as mentioned here: https://wiki.postgresql.org/wiki/Parallel_Query_Execution