Re: Recursive Left Joins Causing Trouble in 8.2.3 RESOLVED (kind of)

["Ian Harding" <harding(dot)ian(at)gmail(dot)com>]

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(dot)ian(at)gmail(dot)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.