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
Show quoted text
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.
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
Show quoted text
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.
Import Notes
Resolved by subject fallback
"Ian Harding" <harding.ian@gmail.com> writes:
I had views that used syntax like
WHERE datecol < current_date and (otherdatecol is null or otherdatecolcurrent_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 otherdatecolcurrent_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