AW: Concurrent CTE

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(dot)formella(at)tme3c(dot)com>
> Gesendet: Dienstag, 3. April 2018 22:01
> An: pgsql-general(at)lists(dot)postgresql(dot)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
>
>