Re: Parallel queries in single transaction

Hi,

On 07/16/2018 09:45 AM, Paul Muntyanu wrote:
> Hello,
>
>    I am working with data warehouse based on postgresql and would like
> to propose a feature. The idea is to give control and ability for
> developer to execute queries in parallel within single transaction.
> Usual flow is next: START_TRANSACTION -> QUERY1 -> QUERY2 -> QUERY3 ->
> END_TRANSACTION. However sometimes QUERY1 and QUERY2 are independent and
> can be executed in parallel mode. E.g.: START_TRANSACTION ->
> DEFINE_QUERY1(no execution) -> DEFINE_QUERY2(no_execution) ->
> EXECUTE_QUERY1_AND_QUERY2(in parallel) -> QUERY3 -> END
>
> Of course QUERY1 and QUERY2 can be dependent and then this would not
> work, but sometimes it is useful, especially when you have bound to e.g.
> CPU and query stuck.

I'm struggling to understand what would be the possible benefits. Either
the queries are CPU-bound or stuck (e.g. waiting for I/O), they can't be
both at the same time. If a query is stuck, running it concurrently is
pointless. If they are CPU-bound, we can run them in parallel (which
should produce the results faster).

I'd even dare to say that running the queries concurrently can easily
hinder performance, because the queries will compete for parallel
workers, preventing some of them from running in parallel mode.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services