Tweak that made my SQL query perform better
It was stormy evening. I could see the light drizzle turning into a strong rain. Dark clouds made the sunset darker than usual and the idea of stepping out for daily errands surely needed to be postponed.
As as a data specialist I had a different storm brewing . One of my customer wanted a report that needed to be delivered quickly to analyze the subscriber behavior for the previous year.
I skimmed through the requirement and started customizing the SQL query while deliberating the scope. After spending few hours of tweaking the joins ,the filters, the aggregates etc. on a relatively smaller filtered dataset I was convinced on firing it on the production data.
I connected the production database, pressed the return key and waited endlessly watching the clock tick 5 mins to 10 mins to 30 mins t an hour with no output.
My heart started racing and I faced the classic dilemma of any developer executing a code on production that seems to go for eternity, to kill or not to kill! (the execution of course)
I tried to look back if we missed any optimization step and realized we did work extensively with the DBAs to go through all the DB optimization strategies, created indexes, partitioned the underlying tables, had the right memory settings and configuration settings, even then, it just didn’t seem to be working.
I wanted to have a second opinion and reached out to a dear colleague with whom I discussed over a call what might be going wrong. We walked through the plan again together, and analyzed each step where the cost was going high. After multiple iterations, we realized the flaw that we had overlooked. He was like ‘Why sequential scan on all the large tables ?’ and together we blurted, should we try the SET ENABLE_SEQSCAN TO OFF before executing the query ?
I executed the above and fired the query and saw the magic happening, It just took few minutes to return the millions of records it was suppose to. I was excited and overwhelmed. I thanked him and downloaded the data to share the report with the customer, after hours of frustration and finally ,I was able to deliver.
So what’s the tweak that helped my SQL query run faster, of course it was the SET ENABLE_SEQSCAN TO OFF (Postgres DB), however for me it was the discussion with the colleague which did the trick to look at the problem with a new pair of eyes and provide an insight that helped me get a good night sleep!
Any developer in the data field has faced similar situation or dreads of facing it sooner or later and sometimes having great colleagues or co workers to discuss and have a walk through can spark some ideas or some clues which can work like magic to overcome similar storm that brewed for me that evening.
