Monthly Archives: January 2017

January 31, 2017 · 2:17 pm

Help wanted!

Since I’ve realized, that our job postings on LinkedIn are not current, I am posting here 3 positions which I personally is most interested to fill. All these people we’ll work very closely with me (for those of you, for whom that’s a plus :))

App Developer

https://www.ziprecruiter.com/jobs/braviant-holdings-20cade2c/senior-software-engineer-b3bc31b7

Database developer

https://www.ziprecruiter.com/jobs/braviant-holdings-20cade2c/database-developer-0c2cf16a

System analyst

https://www.ziprecruiter.com/jobs/braviant-holdings-20cade2c/senior-systems-analyst-ff992fdc

There are matching junior positions for each senior position:)

Leave a comment

Filed under Companies, Workplace

Tagged as

January 30, 2017 · 9:42 pm

The change of venue for ACM/SIGMOD 2017

I understand, that these are old news, and that during the past two weeks plenty of way more important things had happened. But nevertheless I wanted to say, that I was very happy when I received the news which I copy below. Knowing how much time and effort it takes to organize events like this, and how exceptionally difficult is it to re-settle a conference of that size in the new place, i can only say one thing – I am very proud of the community!

Dear members of the SIGMOD/PODS Community:

On January 16 (Martin-Luther-King Day in the USA), the SIGMOD Executive
Committee decided to move the ACM SIGMOD/PODS 2017 conference out of
North Carolina to a new, still undecided location. The decision was made
unanimously by the Committee following extensive discussions with the
SIGMOD Advisory Board.

The SIGMOD/PODS community is open and inclusive. It embraces all forms
of diversity, including diversity of gender identity. Our commitment to
these fundamental principles would have been in question if we held the
SIGMOD/PODS 2017 conference in North Carolina, hence the decision to move.

The decision was triggered by North Carolina’s HB2 bill and the decision
of North Carolina’s policy makers not to repeal HB2 in December 2016.
With its decision, the SIGMOD organization follows the example of many
other organizations (science, industry, sports, and arts) to move major
events that were planned to take place in North Carolina to other
locations. A total of 68 international companies (including many companies
that employ members of the SIGMOD/PODS community around the world) and
several states of the USA have protested and taken a stand against HB2.
The Department of Justice of the United States has filed a lawsuit
against HB2 and the state of North Carolina.

It has also come to our attention that many members of the SIGMOD/PODS
community, including some of our senior leaders, have already decided to
not attend any conference in North Carolina to show solidarity with the
LGBTQ community. A conference without full participation and with only
limited discourse between the members of the community is undesirable.
Furthermore, many of our industrial sponsors are likely to not want to
be affiliated with conferences held in North Carolina.  We do not wish
to strain our relations with our sponsors and friends from industry.

This decision has many practical implications for which we need your
help. We are actively working to find a new venue and will announce the
outcome as soon as possible. In the meantime, please hold back with travel
arrangements. Changing the venue might also involve changing the dates.
If you have already made travel arrangements, please contact us. We are
also looking into issues related to visas. We apologize for any
inconvenience this decision may cause and will try our best to alleviate
any resulting difficulties.

We thank the North Carolina local organizing committee, led by Rada
Chirkova and Jun Yang, for all the work that they have done so far.
Rada and Jun have graciously accepted to continue to serve as general
co-chairs of the relocated conference. Without their help, relocation
would not have been possible. The Program Committee, lead by Dan Suciu,
will continue to organize the program as planned. We are extremely
thankful to all volunteers for their commitment.

The decision to relocate the SIGMOD/PODS 2017 conference was not
taken lightly. It was made after an intense debate over the last two
weeks from which it became clear that there was no easy solution to
the issue that had emerged. We truly hope that this decision will help
unite the SIGMOD/PODS community, sending a message of inclusiveness
and respect for one another.

SIGMOD Executive Committee

Donald Kossmann
Anastasia Ailamaki
Magdalena Balazinska
K. Selcuk Candan
Yanlei Diao
Curtis Dyreson
Yannis Ioannidis
Christian S. Jensen
Jan Van den Bussche

Leave a comment

Filed under events, news

Tagged as

January 23, 2017 · 11:30 pm

Chicago PUG updates

I am not sure how any of those who attended last week’d Meetup are actually reading this blog, but if you are one of those people who came to the Braviant office last week – thank you! And I hope you’ve enjoyed it! I certainly did, I think this event was a great success. Both speakers where just outstanding, and the audience was really engaged.

Here are some pictures:

Continue reading →

Leave a comment

Filed under events, SQL, talks

Tagged as , ,

January 17, 2017 · 7:44 am

Chicago Postgres User Group reminder

Attention Chicago, tomorrow is a day! As announced previously, January 2017 Chicago PUG will take place in the new location – Braviant Holdings office at 180 North Wacker Drive.

As a new co-organizer, I am really excited to host Chicago PUG for the first time. As you can imagine, it’s not only a location which is going to change. We are planning to focus on the actual Postgres users, not just the DBAs and Postgres developers, we want to hear from application developers, data analysts and report writers – anybody how uses Postgres to meet their business needs.

We are going to have presentations for different user levels, from beginners to advance, and we are welcoming any suggestions of topics, or just impromptu questions – there will be always enough professional in the room to get you an answer.

Please RSVP here and join the fun!

Postgres IS a service. We are here to help.

Leave a comment

Filed under events, talks

Tagged as ,

January 15, 2017 · 9:07 pm

One “emergency optimization”

Why it’s an “emergency optimization”? Well, because you write some SQL, you debug and optimize it, and it works fine, and it runs in production ten times during the business hours producing some report… and all of a sudden it just stops working… well, not necessarily “stop”, but start to run more than a hundred times slower… then you need to drop everything you are doing and optimize it ASAP. Because it’s already in production!

Last Thursday I had several things which “stopped working” in a manner described above. I’ve written about one of them in my previous post, but that one was relatively easy to fix. The next one looked as trivial as it can be, but stll I could not make it work!

Here is how the query looked like:

SELECT ...
   FROM payments p WHERE payment_date< current_date 
        AND payment_id NOT IN (SELECT payment_id from bank_records)
        AND...

There were more conditions, but they are not so important. This worked fine until a couple of days ago. What could be more simple? The payment_id field is indexed in both tables, and once again, this query should return a very small number of records. Also, the tables themselves are small, a little bit over 100,000 records. However, all of a sudden it started to run for minutes! When I looked at the execution plan I saw, that instead of doing a merge join utilizing the index, Postgres decided to read the whole bank_records table into the main memory, “materialize”, and then loop through this data set for each and single record of the payments table!

When I saw this, I immediately rewrote the SQL in my favorite way – with OUTER JOIN:

SELECT ...
   FROM payments p 
        LEFT OUTER JOIN bank_records b 
        ON b.payment_id =p.payment_id AND payment_date< current_date 
        AND b.payment_id IS NULL 
        AND...

This didn’t help either – still sequential scan and line by line comparison. And then I decided to use “the last resort”- which I hate!

SELECT ...
   FROM payments p WHERE payment_date< current_date 
        AND NOT EXISTS (SELECT payment_id from bank_records b
        WHERE payment_id=p.payment-id)
        AND...

This SQL just magically started to use the index, and the execution time when down from8 minutes to 300 milliseconds…

Yes, it’s cool… but why?!

2 Comments

Filed under Data management, SQL

Tagged as

January 15, 2017 · 12:31 pm

When things do not work like you expect them to work…

This post has a history. I’ve done the original optimization about three weeks ago, then I wanted to cleanse my example from the company-specific things, then I could not reproduce the original execution plan, and then at the end, when I was ready to publish it… it stopped working in our production system! By “stop working” I mean, that it started to take about 90 seconds to execute…But at the very end, by the time I was done with optimization for the second time, I guess, I was able to formulate some basic principles optimization of this kind of queries… so, without further ado – let’s start!

Each time I talk about optimizing join in Postgres, I advise people to include any additional selection criteria into join condition, even if they are, technically speaking, not “join” conditions. The reason for this suggestion is, that if the optimizer would see any way to pre-filter the records before  joining or while joining, it will be done.

So I was trying to utilize this strategy while working on a query which would check for the following condition:

SELECT ...
FROM loan_contract lc
JOIN loan_details ld on lc.loan_id=ld.loan_id
     AND contract_date >= current_date- interval '10 days' 
WHERE (state ='CA' AND  rate>10) OR (state='MO' AND rate>15)

I knew that this SELECT should retrieve a very small number of records if any, and thereby I was thinking that if I add this condition to the JOIN, it will execute faster because Postgres will be able to filter out the records which are not needed before join… but I was wrong!

SELECT ...
FROM loan_contract lc
JOIN loan_details ld on lc.loan_id=ld.loan_id
     AND contract_date >= current_date- interval '10 days' 
AND((state ='CA' AND  rate>10) OR (state='MO' AND rate>15))

Continue reading →

Leave a comment

Filed under SQL

Tagged as