3

Here and here seem to suggest that they are, but I've also been told (source unlocatable) that a multi-row single-statement insert will only use one thread for all writing, that to multithread it, the query should be broken up and sent to as many connections as there are cores.

The documentation states that CTEs are executed "concurrently", but none linked say explicitly in effect that "CTEs will use all available cores to process any parallel sub-statements and/or the main query".

Do CTEs always use all available cores for any parallel tasks? If so, are there any caveats aside from what could be reasonably assumed from chaining with RETURNING?

  • 1
    Postgres does not run any statement multithreaded. That includes CTEs The "concurrently" is probably only there to indicated that there is no inherent order how they are processed – a_horse_with_no_name Dec 21 '14 at 9:18
  • Multithreaded processing is something you do NOT want in the database(under normal circumstances). Databases goal is to protect data and also to provide reasonable resource sharing. Multithreaded processing would give all the servers resources to a single user, which is something you usually you do not want to happen. – ibre5041 Dec 21 '14 at 9:34
  • @ibre5041 What if there's only one core? – Jim Bob Dec 21 '14 at 9:53
  • Even then you have same CPU time and some disk IOs to be split up among multiple users. If you spend it to perform a single statement then other users will have to wait. – ibre5041 Dec 21 '14 at 17:26
  • 2
    @ibre5041 Huh? There's nothing at all wrong with careful and appropriate use of threading if it can be used to parallelise CPU-heavy portions of queries across multiple cores or to help with I/O wait concurrency in a blocking I/O model. PostgreSQL doesn't support any kind of concurrency within a single session/query, but it'd be nice if it did, as not everyone's workload is pure OLTP (large numbers of fairly simple selects and DML). – Craig Ringer Dec 22 '14 at 2:05
4

Postgres currently does not use parallelism when executing a single query. The entire execution engine is single threaded for each query (you can of course still execute multiple queries in parallel). This lack of "intra query parallelism" is one of the great drawbacks of running Postgres or MySQL. Basically, it makes those engines unfit for purpose on large datasets.

To get multi-threaded Postgres execution, you either need to split up the query into multiple queries yourself or run a Postgres compatible variant like Amazon Redshift or Greenplum (the former has added intra query parallelism to the code base, the latter does the splitting for you).

Whenever you feel the need to run a parallel query, first ask yourself: Could I add indexes that would make this fast enough without parallelism? If that answer is "Yes", pursue that option first. Very often though, you need brute force. And when it comes to brute force, vanilla Postgres is unfortunately not a particularly good database engine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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