Don’t forget the WITH clause in PostgreSQL!
Working all day with PostgreSQL for BigData analysis i always encounter many problems.
Sometimes those problems are really annoying, just like query slow performance.
Performance is a key aspect in Business intelligence
you always need a fresh and fast algorithm that allows you to retrieve many data in few seconds.
Last week was a rough week, many new data to polish control and retrieve; and i’ve encountered a serious problem: i couldn’t query a million rows table (100M rows circa).
Well you can say: “It’s a huge number of rows to get!” and i’ll answer you: “If your investors wants something that could be done you have to find a way!”
How my table was made (I cannot reveal details but i can show you something similar):
I had a similar code:
That’s just an example code remember!
Once i realised that i cannot retrieve any data with this kind of query (remember there where 100M of rows) i started to look for a solution.
Even the SSD cannot save me from this mess because there are:
- 3 JOINS
- 2 MAX with conversions
- 2 SUMS
- 1 COUNT
- And a huge GROUP BY
That’s a lot going on!
How can i optimize the query for such amount of data and kind of operations?
Well searching around Google i’ve found this.
It’s a useful page in which is explained well how PostgreSQL CTE works!
It is a beautiful feature that you can use even for recursive queries such as retrieve a son-parent relationship in a table.
Once i’ve figured out how to write the code i had something like this:
With a query like this you literally change how the query is being processed, you can leave the query optimizer the last decision but you have given the software the order of what query are being processed and then regroup inside the last select outside the WITH clause.
Like this you can benefit of a 500x boost in performance.
I hope that this article is useful for many of you fighting every day with huge table monsters! (And no SSD’s cannot save you forever but that’s a story for another time!)
Thanks for reading.
