How to set number of workers in postgresql select?

Question

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.

Not inside the query. You either set it as a setting in the session, or a setting of the database/whole cluster. — Bergi
– Bergi, Commented Oct 6, 2020 at 12:12
Can I get a hint as to where I can change these settings? I changed the value for paralel_workers in my accounts table using pgAdmin but the query still executes with 2 workers instead of the specified number. — Michael Kročka
– Michael Kročka, Commented Oct 6, 2020 at 12:36
If you want to learn about indexes, then create the index and observe the results rather than taking the "brute force attack" with more workers — user330315
– user330315, Commented Oct 7, 2020 at 8:52

Laurenz Albe · Accepted Answer · 2020-10-06 15:47:57Z

8

You can increase max_parallel_workers_per_gather, or you can

ALTER TABLE accounts SET (parallel_workers = 6);

answered Oct 6, 2020 at 15:47

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Kročka Over a year ago

I understand the sql query as it is simple, but when I run it, the workers used (in the query from the question) are still only 2, not 6. And I can't for the life of me find max_parallel_workers_per_gather anywhere in pgAdmin, or am I supposed to look elsewhere?

Laurenz Albe Over a year ago

That's a configuration patameter in postgresql.conf. Can you edit the question and add the EXPLAIN (ANALYZE) output for the query after you ran the ALTER TABLE?

Michael Kročka Over a year ago

Edited the question with the output.

Collectives™ on Stack Overflow

How to set number of workers in postgresql select?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related