Re: [HACKERS] Parallel query execution with SPI
On 31.03.2017 13:48, Robert Haas wrote: On Fri, Mar 31, 2017 at 3:33 AM, Konstantin Knizhnik wrote: It is possible to execute query concurrently using SPI? If so, how it can be enforced? I tried to open cursor with CURSOR_OPT_PARALLEL_OK flag but it doesn't help: query is executed by single backend while the same query been launched at top level uses parallel plan: fsstate->portal = SPI_cursor_open_with_args(NULL, fsstate->query, fsstate->numParams, argtypes, values, nulls, true, CURSOR_OPT_PARALLEL_OK); ... SPI_cursor_fetch(fsstate->portal, true, 1); Parallel execution isn't possible if you are using a cursor-type interface, because a parallel query can't be suspended and resumed like a non-parallel query. If you use a function that executes the query to completion in one go, like SPI_execute_plan, then it's cool. See also commit 61c2e1a95f94bb904953a6281ce17a18ac38ee6d. Thank you very much for explanation. In case of using SPI_execute the query is really executed concurrently. But it means that when I am executing some query using SPI, I need to somehow predict number of returned tuples. If it is not so much, then it is better to use SPI_execute to allow concurrent execution of the query. But if it is large enough, then SPI_execute without limit can cause memory overflow. Certainly I can specify some reasonable limit and it if is reached, then use cursor instead. But it is neither convenient, neither efficient. I wonder if somebody can suggest better solution? -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parallel query execution with SPI
On Fri, Mar 31, 2017 at 4:18 PM, Robert Haas wrote: > On Fri, Mar 31, 2017 at 3:33 AM, Konstantin Knizhnik > wrote: >> It is possible to execute query concurrently using SPI? >> If so, how it can be enforced? >> I tried to open cursor with CURSOR_OPT_PARALLEL_OK flag but it doesn't help: >> query is executed by single backend while the same query been launched at >> top level uses parallel plan: >> >> fsstate->portal = SPI_cursor_open_with_args(NULL, fsstate->query, >> fsstate->numParams, argtypes, values, nulls, true, CURSOR_OPT_PARALLEL_OK); >> ... >> SPI_cursor_fetch(fsstate->portal, true, 1); > > Parallel execution isn't possible if you are using a cursor-type > interface, because a parallel query can't be suspended and resumed > like a non-parallel query. If you use a function that executes the > query to completion in one go, like SPI_execute_plan, then it's cool. > See also commit 61c2e1a95f94bb904953a6281ce17a18ac38ee6d. > > -- Adding to that, for your case, passing CURSOR_OPT_PARALLEL_OK is not enough, because PortalRun for the cursor would be having portal->run_once set as false which restricts parallelism in ExecutePlan, if (!execute_once || dest->mydest == DestIntoRel) use_parallel_mode = false; You may check [1] for the discussion on this. [1] https://www.postgresql.org/message-id/flat/CAFiTN-vxhvvi-rMJFOxkGzNaQpf%2BKS76%2Bsu7-sG_NQZGRPJkQg%40mail.gmail.com#cafitn-vxhvvi-rmjfoxkgznaqpf+ks76+su7-sg_nqzgrpj...@mail.gmail.com -- Regards, Rafia Sabih EnterpriseDB: http://www.enterprisedb.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parallel query execution with SPI
On Fri, Mar 31, 2017 at 3:33 AM, Konstantin Knizhnik wrote: > It is possible to execute query concurrently using SPI? > If so, how it can be enforced? > I tried to open cursor with CURSOR_OPT_PARALLEL_OK flag but it doesn't help: > query is executed by single backend while the same query been launched at > top level uses parallel plan: > > fsstate->portal = SPI_cursor_open_with_args(NULL, fsstate->query, > fsstate->numParams, argtypes, values, nulls, true, CURSOR_OPT_PARALLEL_OK); > ... > SPI_cursor_fetch(fsstate->portal, true, 1); Parallel execution isn't possible if you are using a cursor-type interface, because a parallel query can't be suspended and resumed like a non-parallel query. If you use a function that executes the query to completion in one go, like SPI_execute_plan, then it's cool. See also commit 61c2e1a95f94bb904953a6281ce17a18ac38ee6d. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers