Thread: Concurrent CTE
Hello! We have a lot of big CTE (~40 statements, ~1000 lines) for very dynamic OLTP content and avg response time 50-300ms. Our setup has 96 threads (Intel Xeon Gold 6128), 256 GB RAM and 12 SSD (3 tablespaces). DB size < RAM. Simplifying the problem: WITH aa as ( SELECT * FROM table1 ), bb ( SELECT * FROM table2 ), cc ( SELECT * FROM table3 ), dd ( SELECT * FROM aa,bb ), ee ( SELECT * FROM aa,bb,cc ), ff ( SELECT * FROM ee,dd ), gg ( SELECT * FROM table4 ), hh ( SELECT * FROM aa ) SELECT * FROM gg,hh,ff /* primary statement */ 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 Table1, table2 and table3 are located on separate tablespaces and are independent. Partial results (aa,bb,cc,dd,ee) are quite big and slow (full text search, arrays, custom collations, function scans...). We consider resigning from the CTE and rewrite to RX Java but we are afraid of downloading partial results and sending it back with WHERE IN(...). Thanks! Artur Formella
On Wed, Apr 4, 2018 at 3:20 AM Artur Formella <a.formella@tme3c.com> wrote:
Hello!
We have a lot of big CTE (~40 statements, ~1000 lines) for very dynamic
OLTP content and avg response time 50-300ms. Our setup has 96 threads
(Intel Xeon Gold 6128), 256 GB RAM and 12 SSD (3 tablespaces). DB size <
RAM.
Simplifying the problem:
WITH aa as (
SELECT * FROM table1
), bb (
SELECT * FROM table2
), cc (
SELECT * FROM table3
), dd (
SELECT * FROM aa,bb
), ee (
SELECT * FROM aa,bb,cc
), ff (
SELECT * FROM ee,dd
), gg (
SELECT * FROM table4
), hh (
SELECT * FROM aa
)
SELECT * FROM gg,hh,ff /* primary statement */
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
Table1, table2 and table3 are located on separate tablespaces and are
independent.
Partial results (aa,bb,cc,dd,ee) are quite big and slow (full text
search, arrays, custom collations, function scans...).
We consider resigning from the CTE and rewrite to RX Java but we are
afraid of downloading partial results and sending it back with WHERE
IN(...).
Thanks!
Artur Formella
It is very difficult from your example to tell just what kind of data you are querying and why you are doing it this way. I will give it a try.
If you are filtering any of this data later you are fencing off that optimization. Also in your example it makes no sense to have cte aa when you could just cross join table1 directly in all your other ctes (and bb and cc for the same reason).
Also in my experience, you are not going to have a great query plan with that many CTEs. Also are you using functions or prepared statements or are you paying the price of planning this query every time?
It is hard to tell but your example leads me to question if there are some serious issues in your db design. Where are your joins and where are you leveraging indexes? Also it is very easy to misuse use a raise and function scans to even make performance worse.
Thanks,
Jeremy
On Tuesday, April 3, 2018, Artur Formella <a.formella@tme3c.com> wrote:
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
If and how depends greatly on your version.
David J.
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
Parallel query can't be used for CTE queries currently.
A pointer to the location in the docs covering this limitation would be appreciated. It isn't covered here:
David J.
On Thu, Apr 5, 2018 at 5:16 PM, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Wed, Apr 4, 2018 at 10:12 PM, Thomas Munro > <thomas.munro@enterprisedb.com> wrote: >> >> Parallel query can't be used for CTE queries currently. > > A pointer to the location in the docs covering this limitation would be > appreciated. It isn't covered here: > > https://www.postgresql.org/docs/10/static/when-can-parallel-query-be-used.html https://www.postgresql.org/docs/10/static/parallel-safety.html -- Thomas Munro http://www.enterprisedb.com
Did you look at this approach using dblink already? https://gist.github.com/mjgleaso/8031067 In your situation, you will have to modify the example but it may give an idea where to start. Klaus > -----Ursprüngliche Nachricht----- > Von: Artur Formella <a.formella@tme3c.com> > Gesendet: Dienstag, 3. April 2018 22:01 > An: pgsql-general@lists.postgresql.org > Betreff: Concurrent CTE > > Hello! > We have a lot of big CTE (~40 statements, ~1000 lines) for very dynamic OLTP > content and avg response time 50-300ms. Our setup has 96 threads (Intel > Xeon Gold 6128), 256 GB RAM and 12 SSD (3 tablespaces). DB size < RAM. > Simplifying the problem: > > WITH aa as ( > SELECT * FROM table1 > ), bb ( > SELECT * FROM table2 > ), cc ( > SELECT * FROM table3 > ), dd ( > SELECT * FROM aa,bb > ), ee ( > SELECT * FROM aa,bb,cc > ), ff ( > SELECT * FROM ee,dd > ), gg ( > SELECT * FROM table4 > ), hh ( > SELECT * FROM aa > ) > SELECT * FROM gg,hh,ff /* primary statement */ > > 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 > > Table1, table2 and table3 are located on separate tablespaces and are > independent. > Partial results (aa,bb,cc,dd,ee) are quite big and slow (full text search, arrays, > custom collations, function scans...). > > We consider resigning from the CTE and rewrite to RX Java but we are afraid > of downloading partial results and sending it back with WHERE IN(...). > > Thanks! > > Artur Formella > >
Can you pass full query & how many rows each table has & how often the tables change & full explain ?
On Thu, Apr 5, 2018 at 8:01 AM, <kpi6288@gmail.com> wrote:
Did you look at this approach using dblink already?
https://gist.github.com/mjgleaso/8031067
In your situation, you will have to modify the example but it may give an idea where to start.
Klaus
> -----Ursprüngliche Nachricht-----
> Von: Artur Formella <a.formella@tme3c.com>
> Gesendet: Dienstag, 3. April 2018 22:01
> An: pgsql-general@lists.postgresql.org
> Betreff: Concurrent CTE>
> Hello!
> We have a lot of big CTE (~40 statements, ~1000 lines) for very dynamic OLTP
> content and avg response time 50-300ms. Our setup has 96 threads (Intel
> Xeon Gold 6128), 256 GB RAM and 12 SSD (3 tablespaces). DB size < RAM.
> Simplifying the problem:
>
> WITH aa as (
> SELECT * FROM table1
> ), bb (
> SELECT * FROM table2
> ), cc (
> SELECT * FROM table3
> ), dd (
> SELECT * FROM aa,bb
> ), ee (
> SELECT * FROM aa,bb,cc
> ), ff (
> SELECT * FROM ee,dd
> ), gg (
> SELECT * FROM table4
> ), hh (
> SELECT * FROM aa
> )
> SELECT * FROM gg,hh,ff /* primary statement */
>
> 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
>
> Table1, table2 and table3 are located on separate tablespaces and are
> independent.
> Partial results (aa,bb,cc,dd,ee) are quite big and slow (full text search, arrays,
> custom collations, function scans...).
>
> We consider resigning from the CTE and rewrite to RX Java but we are afraid
> of downloading partial results and sending it back with WHERE IN(...).
>
> Thanks!
>
> Artur Formella
>
>