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