Parallel Queries Not Launching Workers

[mikewang333]

Hi,

When I try to take advantage of parallel queries, I'm noticing that the planned number of workers is 2 but 0 end up getting launched and my performance is slow.

You can repro this with any GATHER MERGE like related queries e.g.:

CREATE TABLE test_gather_merge AS
SELECT
    generate_series AS id,
    md5(random()::text) AS random_text
FROM
    generate_series(1, 1000000);  -- Adjust the number of rows as needed

-- Query with ORDER BY to induce sorting with parallel processing
SELECT id, random_text
FROM test_gather_merge
ORDER BY random_text
LIMIT 100;

I've already tried upping things like max_parallel_workers and max_worker_processes and I noticed that with other postgres clients (e.g. Postico) the parallel workers are launched successfully so I assume it's not server related.

Strangely enough if I run the queries with "Explain Analyze" in front, the parallel workers are launched successfully and execution is much faster.

Anyone have any idea why parallel queries using this SDK are not executed with parallel workers?

Thanks!

[charmander]

How do you get the information in the screenshot without using EXPLAIN ANALYZE? Are you passing any additional configuration with the query, e.g. rows as mentioned in the PostgreSQL documentation (show the complete code of a reproducer)?

I can’t reproduce this with pg 8.13.1, Node 22.11.0, PostgreSQL 17.0:

const {Client} = (await import('pg')).default;

const client = new Client({host: 'db', user: 'postgres'});

const sql = `SELECT id, random_text
FROM test_gather_merge
ORDER BY random_text
LIMIT 100;`;

const measure = async (name, opts) => {
    const start = performance.now();
    await client.query({text: sql, ...opts});
    const end = performance.now();
    console.log(`${name}: ${(end - start).toFixed(1)} ms`);
};

await client.connect();

console.log((await client.query('SHOW max_parallel_workers')).rows[0]);

await measure('warm up', {});
await measure('plain', {});
await measure('rows', {rows: 100});
await measure('explain', {text: `EXPLAIN ANALYZE ${sql}`});

await client.query('SET max_parallel_workers TO 0');

await measure('serial', {});

await client.end();

{ max_parallel_workers: '8' }

test	time
warm up	36.4 ms
plain	34.1 ms
rows	83.9 ms
explain	240.7 ms
serial	82.1 ms

[mikewang333]

Ah I realize it's happening only when I stream in my results (i.e. using https://www.npmjs.com/package/pg-query-stream). Without streaming my postgres queries seem to take advantage of parallel workers--sorry I didn't notice this earlier!

I assume we can't take advantage of parallel workers when streaming because we're only pulling in a subset of the data at a time but lmk if you know of any workarounds. And feel free to close this issue 🙏

[charmander]

Right, that’s a current server-side limitation that pg can’t help with.