Re: decompose big queries

On Fri, Apr 6, 2018 at 9:35 AM, pinker <pinker(at)onet(dot)eu> wrote:

> Edson Carlos Ericksson Richter wrote
> > I don't know if there are best practices (each scenario requires its own
> > solution), but for plain complex SELECT queries, I do use "WITH"
> > queries... They work really well.
>
> Be cautious with CTE's. They weren't meant to be an alternative to
> subqueries and will probably change the way your query is executed, because
> they are optimisation fences:
> https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f184378
> 0.html
>
>
Often times, large queries like the example you have provided
were written as a generic query to obtain information for an
application. So developers should be cautioned to write queries
that are specific to the data needed for each situation.

Objectively speaking, you should look at two main areas.
First, examine the WHERE clause. Sometimes there are
redundant restrictions which can be removed.
Next, look at the columns that are selected. Are all those
columns really needed?
After you have cleaned the WHERE clause and columns, you
may find it is no longer necessary to join so many tables.

On Fri, Apr 6, 2018 at 9:35 AM, pinker <pinker(at)onet(dot)eu> wrote:

> Edson Carlos Ericksson Richter wrote
> > I don't know if there are best practices (each scenario requires its own
> > solution), but for plain complex SELECT queries, I do use "WITH"
> > queries... They work really well.
>
> Be cautious with CTE's. They weren't meant to be an alternative to
> subqueries and will probably change the way your query is executed, because
> they are optimisation fences:
> https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f184378
> 0.html
>
>

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!