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 > >
