When working with large datasets in PostgreSQL, query performance becomes one of the most important topics to understand. In real production systems such as ERP applications built on Odoo, a single ORM-generated query can process millions of rows and trigger PostgreSQL’s parallel execution engine.
This article explains how PostgreSQL parallel workers execute a query in parallel by recreating a real slow-query scenario using two large tables:
- user_session_history
- session_product_list
We will build the tables, insert millions of rows, run the query, inspect pg_stat_activity, and understand how the leader process and parallel workers coordinate internally.
Many developers run a slow query and only look at the execution time. But PostgreSQL gives much deeper visibility:
- How many workers were launched
- Which process is the leader
- What workers are waiting for
- Whether communication between processes is happening
- Why does the query behave differently on different systems
Understanding this helps with:
- Performance tuning
- Debugging production issues
- Better index design
- Reading PostgreSQL internals
- Improving ORM-generated queries
Real Scenario
A query generated by an application was slow on a local laptop but faster on Amazon Aurora.
Approximate timings:
- Aurora PostgreSQL: around 30 seconds
- Local laptop: around 3 minutes with timeout symptoms
The query used two very large tables:
- user_session_history ˜ 8 million rows
- session_product_list ˜ 15 million rows
The query:
SELECT company_id, package_id, stock_id
FROM user_session_history
WHERE session_id NOT IN (
SELECT id FROM session_product_list
)
ORDER BY id ASC
LIMIT 80;
Step 1: Create the Test Tables
DROP TABLE IF EXISTS user_session_history;
DROP TABLE IF EXISTS session_product_list;
CREATE TABLE user_session_history (
id BIGSERIAL PRIMARY KEY,
company_id INT,
package_id INT,
stock_id INT,
session_id BIGINT,
created_at TIMESTAMP DEFAULT now()
);
CREATE TABLE session_product_list (
id BIGSERIAL PRIMARY KEY,
product_id INT,
created_at TIMESTAMP DEFAULT now()
);
Step 2: Insert 8 Million Rows into user_session_history
INSERT INTO user_session_history
(company_id, package_id, stock_id, session_id)
SELECT
(random()*10)::int,
(random()*100)::int,
(random()*1000)::int,
gs
FROM generate_series(1,8000000) gs;
What This Does
- Generates rows from 1 to 8,000,000
- Creates randomized values for company, package, and stock
- Uses the generated number as session_id
This gives us a realistic, large table for scanning.
Step 3: Insert 15 Million Rows into session_product_list
INSERT INTO session_product_list
(product_id)
SELECT
(random()*5000)::int
FROM generate_series(1,15000000);
Now, both tables are large enough to trigger expensive plans.
Step 4: Update Statistics
ANALYZE user_session_history;
ANALYZE session_product_list;
Why ANALYZE Matters
PostgreSQL uses statistics to estimate:
- Table size
- Row counts
- Data distribution
- Whether parallel execution is useful
Without updated stats, the planner may choose a poor plan.
Step 5: Encourage Parallel Execution
To clearly observe parallel workers in a test environment:
SET max_parallel_workers_per_gather = 8;
SET max_parallel_workers = 8;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;
SET work_mem = '256MB';
Purpose of These Settings
max_parallel_workers_per_gather
Maximum workers a single query can use.
For more info, check the metadata of this configuration parameter like this
select * from pg_settings where name = 'max_parallel_workers_per_gather';
Result :
-[ RECORD 1 ]---+-----------------------------------------------------------------
name | max_parallel_workers_per_gather
setting | 8
unit |
category | Resource Usage / Worker Processes
short_desc | Sets the maximum number of parallel processes per executor node.
extra_desc |
context | user
vartype | integer
source | session
min_val | 0
max_val | 1024
enumvals |
boot_val | 2
reset_val | 2
sourcefile |
sourceline |
pending_restart | f
max_parallel_workers
Total parallel workers allowed by the server.
select * from pg_settings where name = 'max_parallel_workers';
Result :
-[ RECORD 1 ]---+----------------------------------------------------------------------------
name | max_parallel_workers
setting | 8
unit |
category | Resource Usage / Worker Processes
short_desc | Sets the maximum number of parallel workers that can be active at one time.
extra_desc |
context | user
vartype | integer
source | session
min_val | 0
max_val | 1024
enumvals |
boot_val | 8
reset_val | 8
sourcefile |
sourceline |
pending_restart | f
parallel_setup_cost
Cost of starting workers. Lowering it encourages parallel plans.
select * from pg_settings where name = 'parallel_setup_cost';
Result :
-[ RECORD 1 ]---+--------------------------------------------------------------------------------------------
name | parallel_setup_cost
setting | 0
unit |
category | Query Tuning / Planner Cost Constants
short_desc | Sets the planner's estimate of the cost of starting up worker processes for parallel query.
extra_desc |
context | user
vartype | real
source | session
min_val | 0
max_val | 1.79769e+308
enumvals |
boot_val | 1000
reset_val | 1000
sourcefile |
sourceline |
pending_restart | f
parallel_tuple_cost
Cost of transferring tuples from workers to the leader.
select * from pg_settings where name = 'parallel_tuple_cost';
Result :
-[ RECORD 1 ]---+---------------------------------------------------------------------------------------------------
name | parallel_tuple_cost
setting | 0
unit |
category | Query Tuning / Planner Cost Constants
short_desc | Sets the planner's estimate of the cost of passing each tuple (row) from worker to leader backend.
extra_desc |
context | user
vartype | real
source | session
min_val | 0
max_val | 1.79769e+308
enumvals |
boot_val | 0.1
reset_val | 0.1
sourcefile |
sourceline |
pending_restart | f
min_parallel_table_scan_size
Minimum table size before considering parallel scan.
select * from pg_settings where name = 'min_parallel_table_scan_size';
Result :
-[ RECORD 1 ]---+------------------------------------------------------------------------------------------------------------------------------------------
name | min_parallel_table_scan_size
setting | 0
unit | 8kB
category | Query Tuning / Planner Cost Constants
short_desc | Sets the minimum amount of table data for a parallel scan.
extra_desc | If the planner estimates that it will read a number of table pages too small to reach this limit, a parallel scan will not be considered.
context | user
vartype | integer
source | session
min_val | 0
max_val | 715827882
enumvals |
boot_val | 1024
reset_val | 1024
sourcefile |
sourceline |
pending_restart | f
work_mem
Memory available for sorts, hashes, and other operations.
select * from pg_settings where name = 'work_mem';
Result :
-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------------
name | work_mem
setting | 262144
unit | kB
category | Resource Usage / Memory
short_desc | Sets the maximum memory to be used for query workspaces.
extra_desc | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files.
context | user
vartype | integer
source | session
min_val | 64
max_val | 2147483647
enumvals |
boot_val | 4096
reset_val | 4096
sourcefile |
sourceline |
pending_restart | f
Step 6: Run the Query
SELECT company_id, package_id, stock_id
FROM user_session_history
WHERE session_id NOT IN (
SELECT id FROM session_product_list
)
ORDER BY id ASC
LIMIT 80;
This is the same style of query that caused the slow performance.
Now, check the current running activity of this in another session.
select * from pg_stat_activity where state = 'active';
Result
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start |
query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id |
query | backend_type
-----------------------+-----------------
5 | postgres | 125236 | 21588 | 10 | cybrosys | psql | | | | 2026-04-23 21:16:49.859712+05:30 | 2026-04-23 21:16:49.858028+05:30 | 2026-
04-23 21:16:49.858028+05:30 | 2026-04-23 21:16:49.863647+05:30 | IPC | MessageQueueSend | active | | 1712 | | SELECT company_id,package_id,stock_id FROM user_sessi
on_history WHERE session_id NOT IN (SELECT id FROM session_product_list)ORDER BY id ASC LIMIT 80; | parallel worker
5 | postgres | 125240 | 21588 | 10 | cybrosys | psql | | | | 2026-04-23 21:16:49.861073+05:30 | 2026-04-23 21:16:49.858028+05:30 | 2026-
04-23 21:16:49.858028+05:30 | 2026-04-23 21:16:49.863926+05:30 | IPC | MessageQueueSend | active | | 1712 | | SELECT company_id,package_id,stock_id FROM user_sessi
on_history WHERE session_id NOT IN (SELECT id FROM session_product_list)ORDER BY id ASC LIMIT 80; | parallel worker
5 | postgres | 125239 | 21588 | 10 | cybrosys | psql | | | | 2026-04-23 21:16:49.860787+05:30 | 2026-04-23 21:16:49.858028+05:30 | 2026-
04-23 21:16:49.858028+05:30 | 2026-04-23 21:16:49.863646+05:30 | IPC | MessageQueueSend | active | | 1712 | | SELECT company_id,package_id,stock_id FROM user_sessi
on_history WHERE session_id NOT IN (SELECT id FROM session_product_list)ORDER BY id ASC LIMIT 80; | parallel worker
5 | postgres | 125238 | 21588 | 10 | cybrosys | psql | | | | 2026-04-23 21:16:49.860388+05:30 | 2026-04-23 21:16:49.858028+05:30 | 2026-
04-23 21:16:49.858028+05:30 | 2026-04-23 21:16:49.863647+05:30 | IPC | MessageQueueSend | active | | 1712 | | SELECT company_id,package_id,stock_id FROM user_sessi
on_history WHERE session_id NOT IN (SELECT id FROM session_product_list)ORDER BY id ASC LIMIT 80; | parallel worker
5 | postgres | 125235 | 21588 | 10 | cybrosys | psql | | | | 2026-04-23 21:16:49.859517+05:30 | 2026-04-23 21:16:49.858028+05:30 | 2026-
04-23 21:16:49.858028+05:30 | 2026-04-23 21:16:49.863646+05:30 | IPC | MessageQueueSend | active | | 1712 | | SELECT company_id,package_id,stock_id FROM user_sessi
on_history WHERE session_id NOT IN (SELECT id FROM session_product_list)ORDER BY id ASC LIMIT 80; | parallel worker
5 | postgres | 125237 | 21588 | 10 | cybrosys | psql | | | | 2026-04-23 21:16:49.859955+05:30 | 2026-04-23 21:16:49.858028+05:30 | 2026-
04-23 21:16:49.858028+05:30 | 2026-04-23 21:16:49.863648+05:30 | IPC | MessageQueueSend | active | | 1712 | | SELECT company_id,package_id,stock_id FROM user_sessi
on_history WHERE session_id NOT IN (SELECT id FROM session_product_list)ORDER BY id ASC LIMIT 80; | parallel worker
5 | postgres | 120670 | | 10 | cybrosys | psql | | | -1 | 2026-04-23 21:14:07.056784+05:30 | 2026-04-23 21:16:59.134874+05:30 | 2026-
04-23 21:16:59.134874+05:30 | 2026-04-23 21:16:59.134878+05:30 | | | active | | 1712 | | select * from pg_stat_activity where state = 'active'
; | client backend
5 | postgres | 21588 | | 10 | cybrosys | psql | | | -1 | 2026-04-23 20:13:50.214141+05:30 | 2026-04-23 21:16:49.858028+05:30 | 2026-
04-23 21:16:49.858028+05:30 | 2026-04-23 21:16:49.858033+05:30 | | | active | | 1712 | | SELECT company_id,package_id,stock_id FROM user_sessi
on_history WHERE session_id NOT IN (SELECT id FROM session_product_list)ORDER BY id ASC LIMIT 80; | client backend
(8 rows)
During execution, active processes looked like this:
- 1 leader process (client backend)
- Multiple rows with parallel worker
- Workers waiting on MessageQueueSend
This is strong evidence that PostgreSQL parallel execution was active.
Why PostgreSQL Uses Parallel Workers Here
The planner sees:
- Very large table scan
- Filtering required
- Sorting required
- Enough data to divide among workers
Instead of using one process to scan millions of rows, PostgreSQL can split the work.
How Parallel Execution Works
The Leader Process
The main session connected to your client becomes the leader.
Responsibilities:
- Start workers
- Coordinate execution
- Receive rows from workers
- Merge and sort results
- Return final rows to client
Parallel Workers
Worker processes are temporary helper backends.
Responsibilities:
- Scan assigned table blocks
- Apply filters
- Produce matching rows
- Send rows back to the leader.
Understanding MessageQueueSend
Workers showed:
wait_event_type = IPC
wait_event = MessageQueueSend
The worker is trying to send rows to the leader through an internal shared memory message queue.
IPC means Inter-Process Communication.
PostgreSQL processes do not directly share local memory, so they use controlled shared memory queues.
Why Workers Wait on MessageQueueSend
This usually means:
- Worker scanned rows
- Worker found matching tuples
- Worker is sending rows
- Leader has not consumed them fast enough yet
The queue can become temporarily full, so the worker waits. This is not automatically an error. It is part of normal coordination.
What the Leader Is Doing at That Time
While workers wait, the leader may be busy with:
- Receiving tuples
- Merging worker streams
- Applying ORDER BY id
- Enforcing LIMIT 80
- Final output generation
Leader Process
|
------------------------------------------
| | | | |
Worker1 Worker2 Worker3 Worker4 Worker5
- Each worker scans part of the table
- Each worker filters rows
- Each worker sends rows to the leader
Leader merges, sorts, and returns 80 rows
Why the Query Is Expensive
1. Large Table Scan
The query works with tables containing millions of rows. To find matching or non-matching records, PostgreSQL may need to read a significant portion of those tables. The more data that must be scanned, the more CPU, memory, and disk I/O are required.
2. NOT IN Subquery
The condition using NOT IN can be costly because PostgreSQL must compare values from the main table against values returned by the subquery. In large datasets, this comparison step can become expensive. In addition, NULL values can affect how the condition is evaluated, which may lead the planner to choose a heavier execution strategy.
3. ORDER BY
The query requests rows in ascending order by id. If PostgreSQL cannot use an efficient index path, it may need to sort a large number of candidate rows before returning the final result. Sorting large result sets increases memory usage and execution time.
4. LIMIT Does Not Always Mean Low Work
Although the query returns only 80 rows, PostgreSQL may still need to process far more data before it can determine which 80 rows satisfy the filter and come first in the requested order. The final output is small, but the work required to produce it can still be substantial.
To see actual execution details:
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT company_id, package_id, stock_id
FROM user_session_history
WHERE session_id NOT IN (
SELECT id FROM session_product_list
)
ORDER BY id ASC
LIMIT 80;
This reveals:
- Worker count
- Gather node details
- Actual timing
- Rows processed
- Buffer hits and reads
- Sort methods
Parallel workers do not magically make every query fast. They reduce scan time by dividing work, but the leader still has to combine results. If sorting, anti-joins, or row transfer become bottlenecks, performance can still suffer.
The best results come from combining:
- Good query design
- Correct indexes
- Updated statistics
- Enough hardware resources
- Understanding PostgreSQL internals
If you want to truly understand PostgreSQL performance, do not stop at execution time. Observe what PostgreSQL is doing internally. A single wait event, such as MessageQueueSend, can reveal the full story of leader-worker coordination, parallel scans, and where the real bottleneck lives.