Monthly Archives: February 2020

A Word About JOIN_COLLAPSE_LIMIT

I remember when I learned about JOIN_COLLAPSE_LIMIT parameter in Postgres. And I remember the unfortunate circumstances of that encounter. Specifically, it was set to eight, which caused a relatively simple SELECT to be executed in a completely wrong way. 

Since I didn’t know anything about that parameter at the moment, it looked to me like the order of joins in the SELECT statements in Postgres matters, and it sounded very wrong and the opposite of what documentation said.

But then I learned about JOIN_COLLAPSE_LIMIT, and suddenly all the execution plans magically fixed themselves after I changed it for 12. I performed a lot of experiments back then trying to find an optimal value for that parameter, and at the end of the day I decided, that no matter how many tables I am joining, changing it to anything above 12 does not help. Either the execution plan does not improve (you have 16 tables in the join, and you think you change the limit to 16, and the query would fix itself – and it does not). Or, if you change it to more than 20, the optimization takes too much time.

However, a couple of weeks ago, somebody requested my help in query optimization. The problem was that it was not a single query, but rather a query framework, and a person would run several queries in sequence, each time adding or removing search criteria that could belong to any of the tables involved in this SELECT.

Yes, “if it would be me,” I would write the SELECT statement differently each time, but that was not something I could suggest to a person who asked for my help. That would be too complicated. I looked at the query, estimated the number of joins and suggested, that at the beginning of each session they would execute 

SET JOIN_COLLAPSE_LMIT=16

And it worked!!!! I do not know what’s so funny in the fact that it worked as expected… but I was really amused 🙂

2 Comments

Filed under SQL

New Bitemporal Correction

It might take several more days, till the new ll_bitemporal_correction function will appear in pg_bitemporal GitHub repo, but it is ready. I finished development and basic testing, and it works.

It means nothing for those who do not care about “the state of bitemporality”, but I know that some people care. For those people, I am happy to report, that finally I delivered what you guys have asked for a long time. Now, you can make bitemporal correction not just to the last effective interval, but to any stretch of time. It may cover several historical intervals, it may start in the middle. It can’t end i the middle, because I didn’t come up with any business case for such situation, but if there will be a demand, I am sure I can provide :). Overall – I am very happy about this result :). Stay tunes – I will post when it will be officially available.

On a different note – we are almost P12 compatible, only bitemporal referential integrity still does not work, but it will be fixed shortly.

Huge thank you for everybody who show interest in this activity:)

2 Comments

Filed under news, SQL