Create a table based on view - the fastest method, use multi threading

Question

I have a view that pulls around 1mil of records; and it takes around 15 minutes for temp table to be created. Nevertheless, only 1 of my CPU cores shows load, the other 7cores are available. How can I use all cores for this process?

create table feed_03_tmp as SELECT *  FROM feed_03;

Answer 1

PostgreSQL backends are single threaded and there is one backend per client so a single client can only use a single core. However for such a simple query I am pretty sure you are IO bound. If you are IO-bound the one core doing the work will be below 100% load if it is constantly at 100% you are CPU bound.

You could use more then one core by opening multiple connections and let each copy a selection of rows but as I said it is probably not going to help. Note that each connection will be in it's own transaction.

To improve IO you may benefit from storing your temp tables on a seperate drive. To do this create a tablespace on a second drive and add a TABLESPACE clause to your create table statement.

CREATE TABLE feed_03_tmp 
  TABLESPACE my_temp_tablespace
  AS SELECT *  FROM feed_03;

BTW: the postgresql developers are experimenting with introducing multithreading but it probably will take sometime for it to get widely used and make it into a release.