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
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
