Postgresql – Postgres parallel seq scan making high CPU Utilization

parallelismpostgresql-10

When I run a select query which runs on a view in Postgres, it is starting 3 processes for a single query, which is causing high CPU utilization when multiple job runs in parallel.

How to avoid using parallel sequential scans and increase my query performance at the same time?

Best Answer

You can disable parallel query by setting max_parallel_workers_per_gather to 0 in postgresql.conf.

Then the only ways of speeding up processing are

keep the table cached in RAM
avoid a sequential scan by creating an index

If possible, the second way is much better.

Related Solutions

MySQL – Parallel Query Performance Issue

Upgrade to 5.5, then 5.6, then 5.7. You will be able to run more simultaneous queries at each upgrade. Do you really mean "5.1.1"? That was a pre-release for 5.1 from about 2005.
Use summary tables for old, static, data. (It is static after it is INSERTed, correct?) Perhaps summarizing (eg AVG, MAX, MIN) daily readings can be done just after midnight?
Use InnoDB, not MyISAM. Much better sharing of the table. (And it gets better with each version upgrade.)
innodb_buffer_pool_size should be about 5500M for your 8G machine. Is the dataset, including indexes, bigger than that? If so, get more RAM and/or RAID with striping and/or SSDs.
And Summary tables are the main cure for Data Warehouse performance issues. Further discussion . It's not uncommon to speed up queries 10-fold, especially I/O-bound ones.

"How does one solve..." -- Most applications are not doing lots of 3.5-second queries. They are doing 'point queries', which might be 3.5 milliseconds.

Postgresql – Parallel PostgreSQL Queries with R

UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 0  AND id < 10;
 UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 10 AND id < 20;;
  UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 20  AND id < 30;

That's insane especially if the query is not CPU bound. Just run one query with one update.

UPDATE targate_table set col1_target = col1_source
FROM source_table
WHERE (id BETWEEN 0 AND 10)
  OR (id BETWEEN 10 AND 20)
  OR (id BETWEEN 20 AND 30);

PostgreSQL doesn't lock the table for row updates. It only locks the rows. There is no reason to break this up.

Best Answer

Related Solutions

MySQL – Parallel Query Performance Issue

Postgresql – Parallel PostgreSQL Queries with R

Related Question