Thread: Re: Recursive Left Joins Causing Trouble in 8.2.3 RESOLVED (kind of)
OK, here's the deal I had views that used syntax like WHERE datecol < current_date and (otherdatecol is null or otherdatecol > current_date) Kind of a hillbilly version of BETWEEN but it assumes null is INFINITY (except I use date, not timestamp) Suddenly, this is ungodly inefficient in 8.2.3. It worked just fine in 8.1.3. What I had to do to "fix" it was to make a function that did the comparison, lied and marked it immutable, and created functional indexes using that function. All is well now. I would love to hear of a more elegant solution. Just a heads-up in case you use that type of SQL and are upgrading to 8.2.X, it might be a problem. - Ian On 2/21/07, Ian Harding <harding.ian@gmail.com> wrote: > This whole thing strikes me funny since my application has run fine > for 6 years and now I have queries that simply take forever, and even > had one that threw an error (Tom fixed the bug very quickly) Now I > have to run with enable_nestloop off globally which seems to negate > the use of indexes by and large, and makes things slower than they > were under 8.1.3, but at least it runs. >
"Ian Harding" <harding.ian@gmail.com> writes: > I had views that used syntax like > WHERE datecol < current_date and (otherdatecol is null or otherdatecol > > current_date) > Suddenly, this is ungodly inefficient in 8.2.3. It worked just fine in 8.1.3. This complaint is pretty much content-free (especially with the oblique implication that it's got something to do with left joins). Please provide a self-contained test case. regards, tom lane
On 2/21/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Ian Harding" <harding.ian@gmail.com> writes: > > I had views that used syntax like > > WHERE datecol < current_date and (otherdatecol is null or otherdatecol > > > current_date) > > Suddenly, this is ungodly inefficient in 8.2.3. It worked just fine in 8.1.3. > > This complaint is pretty much content-free (especially with the oblique > implication that it's got something to do with left joins). Please > provide a self-contained test case. > > regards, tom lane > True. Yesterday was my last day at that employer, today is my first at my new one. I needed to get things going and was just happy to be able to turn enable_nestloop back on and have things work. I will see if I can get the problem recreated without too much extraneous junk, it was buried pretty deep in views referencing views referencing views which took what may be a minor change in query timing and planning complexity and blowing it out. The odd query structure (I thought) was a series of self left-joins but that may have been smoke. I was just surprised that basically my entire system came to a halt with a minor version upgrade, and that nobody else had seen anything similar. I know I write careless SQL from time to time, but again, I didn't think I was THAT unique! - Ian