Re: Concurrent CTE

[Dorian Hoxha]

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

Re: Concurrent CTE

[David G. Johnston]

On Tuesday, April 3, 2018, Artur Formella  wrote:

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

If and how depends greatly on your version.

https://www.postgresql.org/docs/10/static/parallel-query.html

David J.

Re: Concurrent CTE

[Jeremy Finzel]

On Wed, Apr 4, 2018 at 3:20 AM Artur Formella  wrote:

> 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


It is very difficult from your example to tell just what kind of data you
are querying and why you are doing it this way. I will give it a try.

If you are filtering any of this data later you are fencing off that
optimization. Also in your example it makes no sense to have cte aa when
you could just cross join table1 directly in all your other ctes (and bb
and cc for the same reason).

Also in my experience, you are not going to have a great query plan with
that many CTEs. Also are you using functions or prepared statements or are
you paying the price of planning this query every time?

It is hard to tell but your example leads me to question if there are some
serious issues in your db design. Where are your joins and where are you
leveraging indexes?  Also it is very easy to misuse use a raise and
function scans to even make performance worse.

Thanks,
Jeremy

>