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, <kpi6...@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.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 > > > > > > > >
