In recent versions of PostgreSQL (as of Dec 2013), can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
No, for versions of PostgreSQL prior to v9.6. Please see the PostgreSQL FAQ: How does PostgreSQL use CPU resources?
The PostgreSQL server is process-based (not threaded). Each database session connects to a single PostgreSQL operating system (OS) process. Multiple sessions are automatically spread across all available CPUs by the OS. The OS also uses CPUs to handle disk I/O and run other non-database tasks. Client applications can use threads, each of which connects to a separate database process.
Since version 9.6, portions of some queries can be run in parallel, in separate OS processes, allowing use of multiple CPU cores. Parallel queries are enabled by default in version 10 (max_parallel_workers_per_gather), with additional parallelism expected in future releases.
PostgreSQL 9.6+ onwards, would start to see Parallel-Query finally coming to PostgreSQL.
What's really exciting is that there are reports confirming
near-linear speed-up in some cases, which is pretty impressive!
No. Each connections spawn a separate process on server.
You can "emulate" some parallelism using a threaded procedural language like pljava. Create a java procedure(function) that launches several threads and create the output result using several workers. The backend is syncronized so each worker can update the output asynchronous.
Java has good support for thread coordination/cooperation.
As examples, this would be nice for CPU intensive operations or network length operations.