[postgis-users] Forcibly parallelizing very expensive st_unaryunion in sequential scan on 20 row MultiLineString Table

[Andrew Joseph]

It appears it has nothing to do with TOAST or Postgis. Postgres simply doesn’t seem to parallelize any operations on tables with few rows regardless of function cost, as shown in the easily replicable example below. Anyone know a workaround for this that doesn’t involve using dblink?

CREATE OR REPLACE FUNCTION very_expensive_operation(value anyelement, sleep_time integer=2) RETURNS integer as $$
    BEGIN
        perform pg_sleep(sleep_time);
        return sleep_time;
    END;
$$ LANGUAGE plpgsql immutable strict parallel safe cost 10000;

CREATE UNLOGGED TABLE expensive_rows (
    id  serial PRIMARY KEY,
    value uuid
) WITH (parallel_workers = 8);
INSERT INTO expensive_rows(value) select gen_random_uuid() identifier from generate_series(1,16);

EXPLAIN ANALYSE VERBOSE

    SELECT
        very_expensive_operation(value,2)
    FROM
      expensive_rows
;

Gather  (cost=0.00..5312.12 rows=1700 width=4) (actual time=2010.650..32042.558 rows=16 loops=1)
"  Output: (very_expensive_operation(value, 2))"
  Workers Planned: 8
  Workers Launched: 7
  ->  Parallel Seq Scan on public.expensive_rows  (cost=0.00..5312.12 rows=212 width=4) (actual time=286.078..4575.903 rows=2 loops=7)
"        Output: very_expensive_operation(value, 2)"
        Worker 0:  actual time=0.001..0.001 rows=0 loops=1
        Worker 1:  actual time=0.001..0.001 rows=0 loops=1
        Worker 2:  actual time=0.001..0.001 rows=0 loops=1
        Worker 3:  actual time=2002.537..32031.311 rows=16 loops=1
        Worker 4:  actual time=0.001..0.001 rows=0 loops=1
        Worker 5:  actual time=0.002..0.002 rows=0 loops=1
        Worker 6:  actual time=0.002..0.002 rows=0 loops=1
Planning Time: 0.086 ms
Execution Time: 32042.609 ms


Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20201127/53df9490/attachment.html>