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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
No. Please see the PostgresSQL FAQ: How does PostgreSQL use CPU resources?
No, but there is a workaround. :)
I found parsel (parallel select) plpgsql function, which splits your query based on primary key, then connects to the database via dblink extension and waits for all subqueries.
Author also wrote article about this function: http://geeohspatial.blogspot.com/2013/12/a-simple-function-for-parallel-queries_18.html
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.