CODEWITHSUNDEEP
postgresql
Michael Kročka
Michael Kročka

Reputation: 655

How to set number of workers in postgresql select?

As the title states, I can run the following query:

EXPLAIN ANALYZE SELECT * FROM accounts WHERE screen_name='realDonaldTrump';

and get the output:

Gather  (cost=1000.00..87090.59 rows=1 width=122) (actual time=369.168..383.632 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2   
->  Parallel Seq Scan on accounts  (cost=0.00..86090.49 rows=1 width=122) (actual time=312.741..326.849 rows=0 loops=3)
        Filter: ((screen_name)::text = 'realDonaldTrump'::text)
        Rows Removed by Filter: 1156140 
Planning Time: 0.065 ms 
Execution Time: 383.649 ms

Now my question is how can I transform this query to use more or use less workers? Online I found some parameters like max_parallel_workers, but I am unable to incorporate them into the query. I am running this query from pycharm -> postgresql console.

Edit: After running the query from the comment, this is the output:

Gather  (cost=1000.00..87090.59 rows=1 width=122) (actual time=348.717..403.839 rows=1 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on accounts  (cost=0.00..86090.49 rows=1 width=122) (actual time=311.207..324.566 rows=0 loops=3)
        Filter: ((screen_name)::text = 'realDonaldTrump'::text)
        Rows Removed by Filter: 1156140
Planning Time: 0.563 ms
Execution Time: 403.856 ms

Edit 2: After messing around with the postgresql.conf file and changing values of max_parallel_workers_per_gather and max_parallel_workers and then restarting postgres, nothing changed. Only later after I ran this query:

SET max_parallel_workers_per_gather = 4;

Did the initial sql query run with the requested 4 workers.

Upvotes: 3

Views: 9571

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246653

You can increase max_parallel_workers_per_gather, or you can

ALTER TABLE accounts SET (parallel_workers = 6);

Upvotes: 7

Related Questions