From: | Dorian Hoxha <dorian(dot)hoxha(at)gmail(dot)com> |
---|---|
To: | kpi6288(at)gmail(dot)com |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Concurrent CTE |
Date: | 2018-04-05 14:52:16 |
Message-ID: | CANsFX04jHvvVJBTmVrLZCo2gaq5vo8+qSt2fD4SvOr3TbViWWQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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, <kpi6288(at)gmail(dot)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(dot)formella(at)tme3c(dot)com>
> > Gesendet: Dienstag, 3. April 2018 22:01
> > An: pgsql-general(at)lists(dot)postgresql(dot)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
> >
> >
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adam =?utf-8?Q?Sj=C3=B8gren?= | 2018-04-05 15:19:58 | Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100 |
Previous Message | kpi6288 | 2018-04-05 06:01:32 | AW: Concurrent CTE |