Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
4  
Have you read this? – Jack Douglas Dec 14 '13 at 15:09
1  
You may also be interested in PL/Proxy. – dezso Dec 14 '13 at 21:12
    
@JackDouglas tnx, but it's not about query distribution among more than one core. – Alireza Hos Dec 15 '13 at 6:15
1  
@john.locke the brief list headed "On the server-side, there is already some parallelism" doesn't include any mention of parallel query (and in fact there is no parallelism of the sort you are asking about yet). Of course if you can break your query down into multiple queries you can run them in parallel. – Jack Douglas Dec 15 '13 at 7:45
1  
Good point about breaking queries into multiple ones to distribute them between cores. tnx – Alireza Hos Dec 15 '13 at 9:32
up vote 29 down vote accepted

No. Please see the PostgresSQL 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.

share|improve this answer
    
Why is this the case? Seems like queries could benefit greatly from parallelization. The query planner creates a tree of joins and filters, and siblings of the tree could easily run in parallel. – sudo Mar 23 at 18:49
1  
@sudo: They are adding parallel execution support to some operations, but they do not help in many cases as you might expect them to so the query planner might not choose to use them much. For IO-bound problems multiple threats can exacerbate performance issues by making the resulting IO operations less sequential, even in memory the extra context switches make it not so useful for some tasks. – David Spillett Mar 24 at 14:54

PostgreSQL 9.6+ onwards, would start to see Parallel-Query finally coming to PostgreSQL.

For e.g. Concepts like Parallel Scan / Parallel Join / Parallel Aggregates are now already baked in, with more to come soon.

What's really exciting is that there are reports confirming near-linear speed-up in some cases, which is pretty impressive!

share|improve this answer
1  
Nice! Links you could add to your answer: rhaas.blogspot.co.uk/2015/11/… and depesz.com/2015/11/17/… – ypercubeᵀᴹ Mar 24 at 7:55

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.

https://gist.github.com/mjgleaso/8031067

Author also wrote article about this function: http://geeohspatial.blogspot.com/2013/12/a-simple-function-for-parallel-queries_18.html

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.