In recent versions of PostgreSQL, can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
No. Please see http://wiki.postgresql.org/wiki/FAQ " How does PostgreSQL use CPU resources?
The PostgreSQL server is process-based (not threaded), and uses one operating system process per database session. A single database session (connection) cannot utilize more than one CPU. Of course, multiple sessions are automatically spread across all available CPUs by your operating system. Client applications can easily use threads and create multiple database connections from each thread.
A single complex and CPU-intensive query is unable to use more than one CPU to do the processing for the query. The OS may still be able to use others for disk I/O etc, but you won't see much benefit from more than one spare core. "
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.