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.
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match