Concurrent CTE

From: Artur Formella <a(dot)formella(at)tme3c(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Concurrent CTE
Date: 2018-04-03 20:01:20
Message-ID: db253d4b-322b-e75a-fc00-b5af4da41e6a@tme3c.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kumar, Virendra 2018-04-03 20:12:58 LDAP Bind Password
Previous Message Rob Sargent 2018-04-03 18:12:11 Re: single sql, multiple schemas, one result set