On Wed, Apr 4, 2018 at 8:01 AM, Artur Formella <a.formella@tme3c.com> wrote:
> Execution now:
> time-->
> Thread1: aa | bb | cc | dd | ee | ff | gg | hh | primary
>
> And the question: is it possible to achieve more concurrent execution plan
> to reduce the response time? For example:
> Thread1: aa | dd | ff | primary
> Thread2: bb | ee | gg
> Thread3: cc | -- | hh
Parallel query can't be used for CTE queries currently. Other good
things also don't happen when you use CTEs -- it's an "optimiser
fence" (though there is discussion of changing that eventually).
Maybe try rewriting your query as:
SELECT ...
FROM (SELECT ...) AS aa,
(SELECT ...) AS bb,
...
Note that in the form of parallelism supported in PostgreSQL 10, every
process (we use processes instead of threads) runs the same execution
plan at the same time, but gives each worker only a part of the
problem using disk block granularity, so it looks more like this:
Process1: fragments of aa | fragments of bb | ...
Process2: fragments of aa | fragments of bb | ...
PostgreSQL 11 (not yet released) will introduce an exception that
looks more like what you showed: the Parallel Append operator (for
unions and scans of partitions) can give each worker a different part
of the plan approximately as you showed, but IIUC that's used as a
fallback strategy when it can't use block granularity (because of
technical restrictions). The problem with sub-plan granularity is
that the various sub-plans can finish at different times leaving some
CPU cores with nothing to do while others are still working, whereas
block granularity keeps everyone busy until the work is done and
should finish faster.
--
Thomas Munro
http://www.enterprisedb.com