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.forme...@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
> 
> 



Reply via email to