Search Postgresql Archives

Re: Concurrent CTE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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.formella@xxxxxxxxx>
> 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
>
>





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux