Xarg blog Archive

I spent a couple hours looking into why the cc_users view performs so poorly on postgresql. I narrowed it down to something in the genetic query optimizer but had not really been able to figure out how to get it to go fast no matter how hard I tried. I asked for help on on irc at #postgresql which was not terribly productive but I wrote it all up, asked on the pgsql-performance mailing list, and had the answer in less than an hour from Tom Lane who is the guy responsible for the optimizer. It turns out I was sort of on the wrong track since it was not really the generic query optimizer rather it's that the runtime parameter GEQO_THRESHOLD is also used in a heuristic to decide when to flatten subselects or not, but the answer is the same as I came up with which is to increase GEQO_THRESHOLD.

It always seemed like a kind of cliche when people claimed that's how things happen with open source and given the signal to noise in a lot of places I was not terribly hopeful I was going to get a real solution but I did. Now I feel like I could be in some sort of testimonial commercial about open source. Certainly compared to trying to figure out why my oracle queries were so horrible this is a refreshing experience.