Thread: decompose big queries

Em 05/04/2018 19:39, hmidi slim escreveu:
> Hi,
> I want to know what are the best practice to use in order to decompose 
> a big query which contains so many joins.Is it recommended to use 
> stored procedures ? or is there any other solution?
>

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.

https://www.postgresql.org/docs/9.6/static/queries-with.html


Regards,

Edson

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-f1843780.html

On Fri, 6 Apr 2018, pinker 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/

+1

Recently I had a poorly performing view speed up by a factor of 6x when 
converted from CTEs to to nested subqueries.  In my case, the lack of 
predicate push-down was a real killer.  Pg would labor away to produce an 
enormous intermediate result that was then selected down to a rather 
modest final result set.  Showed up clearly in the access plan, however, 
and wasn't much of a problem to fix.


-- 

> Hi,
> I want to know what are the best practice to use in order to
> decompose a big query which contains so many joins.Is it recommended
> to use stored procedures ? or is there any other solution?

The main problem with SP's is that they can really screw up
optimization. Most of the time you'll get more bang for the
buck by managing the SQL, breaking it into more manageable 
chunks.

Views can be a big help for this. Materialized views can help
by pushing lock-heavy or long-running subqueries into the wee
hours where resources can be managed a bit more easily (e.g.,
you don't collide with interactive queries or may be able to
just lock the tables and be done with it). They also give you 
a more stable dataset to start with (e.g., no need to worry
about inserts causing disconnects in totals or requiring 
table locks to avoid).

Temporary tables can help by breaking out messy portions of
the query and allowing you to pre-filter portions of the result.

Scratch tables (i.e., a permenant table that gets truncated after
use) can also help by staging portions of the query in indexed
areas. With careful use they can be a big help because you can 
pre-aggregate portions of the query into a table with nice, 
specific indexes. 

Many of the worst queries involve reporting on aggregates over time
that are re-calculated for each report. Pre-aggregating on, say, a
daily basis can both simplify the reporting query and allow you to
push some of the work off to the wee hours. You can also get the 
benefit of more specific values that may allow for unique indexes
on the subtotals. 

If the query involves "many" joins there is a good chance that 
they break into clumps of related data. Breaking, say, daily usage
vs. user account vs. accounting data into separate [materialized]
views or temp tables keeps the joins more manageable and helps
release resources that might otherwise get consumed for the entire
query.

-- 
Steven Lembark                                       1505 National Ave
Workhorse Computing                                 Rockford, IL 61103
lembark@wrkhors.com                                    +1 888 359 3508