[PATCH] Allow parallelism for plpgsql return expression after commit 556f7b7

Hello everyone,

With the commit 556f7b7bc18d34ddec45392965c3b3038206bb62, Any plpgsql
function that returns scalar value would not be able to use parallelism to
evaluate a return statement. It will not be considered for parallel
execution because we are passing maxtuples = 2 to exec_run_select from
exec_eval_expr to evaluate the return expression of the function.

Call stake to ExecutePlan -

#0 ExecutePlan (queryDesc=0x589c390, operation=CMD_SELECT,
sendTuples=true, numberTuples=2, direction=ForwardScanDirection,
dest=0xe15ca0 <spi_printtupDR>) at execMain.c:1654

#1 0x000000000075edb6 in standard_ExecutorRun (queryDesc=0x589c390,
direction=ForwardScanDirection, count=2, execute_once=true) at
execMain.c:366

#2 0x00007f5749c9b8d8 in explain_ExecutorRun (queryDesc=0x589c390,
direction=ForwardScanDirection, count=2, execute_once=true) at
auto_explain.c:334

#3 0x000000000075ec25 in ExecutorRun (queryDesc=0x589c390,
direction=ForwardScanDirection, count=2, execute_once=true) at
execMain.c:310

#4 0x00000000007c4a48 in _SPI_pquery (queryDesc=0x589c390,
fire_triggers=true, tcount=2) at spi.c:2980

#5 0x00000000007c44a9 in _SPI_execute_plan (plan=0x5878780,
options=0x7ffc6ad467e0, snapshot=0x0, crosscheck_snapshot=0x0,
fire_triggers=true) at spi.c:2747

#6 0x00000000007c135f in SPI_execute_plan_with_paramlist (plan=0x5878780,
params=0x0, read_only=false, tcount=2) at spi.c:765

#7 0x00007f5749eb4a8b in exec_run_select (estate=0x7ffc6ad46ba0,
expr=0x5892b80, maxtuples=2, portalP=0x0) at pl_exec.c:5840 <-- maxtuples =
2

#8 0x00007f5749eb46fe in exec_eval_expr (estate=0x7ffc6ad46ba0,
expr=0x5892b80, isNull=0x7ffc6ad46bc0, rettype=0x7ffc6ad46bc4,
rettypmod=0x7ffc6ad468e8) at pl_exec.c:5734

Consider the following simple repro –

postgres=# create table test_tab(a int);

CREATE TABLE

postgres=# insert into test_tab (a) SELECT generate_series(1, 1000000);

INSERT 0 1000000

postgres=# analyse test_tab;

ANALYZE

postgres=# create function test_plpgsql() returns int

language plpgsql

as

$$

begin

return (select count(*) from test_tab where a between 5.0 and 999999.0);

end;

$$;

postgres=# LOAD 'auto_explain';

LOAD

postgres=# SET auto_explain.log_min_duration = 0;

SET

postgres=# SET auto_explain.log_analyze = true;

SET

postgres=# SET auto_explain.log_nested_statements = true;

SET

postgres=# select test_plpgsql();

test_plpgsql

--------------

999995

(1 row)

Plan logged in logfile -

Query Text: (select count(*) from test_tab where a between 5.0 and
999999.0)

Result (cost=13763.77..13763.78 rows=1 width=8) (actual
time=912.108..912.110 rows=1 loops=1)

InitPlan 1

-> Finalize Aggregate (cost=13763.76..13763.77 rows=1 width=8)
(actual time=912.103..912.104 rows=1 loops=1)

-> Gather (cost=13763.54..13763.75 rows=2 width=8) (actual
time=912.096..912.098 rows=1 loops=1)

Workers Planned: 2

*Workers Launched: 0*

-> Partial Aggregate (cost=12763.54..12763.55 rows=1
width=8) (actual time=912.095..912.096 rows=1 loops=1)

-> Parallel Seq Scan on test_tab
(cost=0.00..12758.33 rows=2083 width=0) (actual time=0.022..812.253
rows=999995 loops=1)

Filter: (((a)::numeric >= 5.0) AND
((a)::numeric <= 999999.0))

Rows Removed by Filter: 5

Patch to fix this issue is attached. Proposed fix should not cause any
regression because the number of returned rows is anyway being checked
later inside exec_eval_expr(…).

Plan logged after fix –

Query Text: (select count(*) from test_tab where a between 5.0 and 999999.0)

Result (cost=13763.77..13763.78 rows=1 width=8) (actual
time=324.397..328.007 rows=1.00 loops=1)

InitPlan 1

-> Finalize Aggregate (cost=13763.76..13763.77 rows=1 width=8)
(actual time=324.391..327.999 rows=1.00 loops=1)

-> Gather (cost=13763.54..13763.75 rows=2 width=8) (actual
time=324.052..327.989 rows=3.00 loops=1)

Workers Planned: 2

*Workers Launched: 2*

-> Partial Aggregate (cost=12763.54..12763.55 rows=1
width=8) (actual time=320.254..320.255 rows=1.00 loops=3)

-> Parallel Seq Scan on test_tab
(cost=0.00..12758.33 rows=2083 width=0) (actual time=0.029..286.410
rows=333331.67 loops=3)

Filter: (((a)::numeric >= 5.0) AND
((a)::numeric <= 999999.0))

Rows Removed by Filter: 2

Thanks & Regards,

Dipesh