SEO

#1 (**permalink**) 04-10-2008, 11:14 AM

Hi,

I've had this minor bugbear with this part of the docs for a while and
am finally reporting it.

http://www.postgresql.org/docs/8.3/s...pressions.html (And all
back branch documentation)

SELECT ... WHERE x <> 0 AND y/x > 1.5;

But this is safe:

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

A CASE construct used in this fashion will defeat optimization attempts,
so it should only be done when necessary. (In this particular example,
it would be best to sidestep the problem by writing y > 1.5*x instead.)

In-equality transformations do not guarantee that y > 1.5x == y/x >
1.5. This is only true for x>0, y < 1.5*x for x<0. I have not posted a
patch as I'm not sure what is the best way to change the example.

Regards

Russell Smith

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

#2 (**permalink**) 04-10-2008, 11:14 AM

Russell Smith wrote:
> SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
>
> A CASE construct used in this fashion will defeat optimization attempts,
> so it should only be done when necessary. (In this particular example,
> it would be best to sidestep the problem by writing y > 1.5*x instead.)
>
>
> In-equality transformations do not guarantee that y > 1.5x == y/x >
> 1.5. This is only true for x>0

So the proper expression would be

SELECT ... WHERE CASE WHEN x >= 0 THEN y > 1.5*x ELSE y < 1.5*x END;

or

SELECT ... WHERE (x >= 0 AND y > 1.5*x) OR y < 1.5*x;

which obviously isn't simpler. So I suggest that we just delete the
parenthetical note.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

#3 (**permalink**) 04-10-2008, 11:14 AM

Russell Smith <mr-russ@pws.com.au> writes:
> In-equality transformations do not guarantee that y > 1.5x == y/x >
> 1.5. This is only true for x>0, y < 1.5*x for x<0. I have not posted a
> patch as I'm not sure what is the best way to change the example.

Seems a bit nit-picky, but we could change the example to

SELECT ... WHERE x > 0 AND y/x > 1.5;
becomes
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

regards, tom lane

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

#4 (**permalink**) 04-10-2008, 11:14 AM

Tom Lane wrote:
> Russell Smith <mr-russ@pws.com.au> writes:
>
>> In-equality transformations do not guarantee that y > 1.5x == y/x >
>> 1.5. This is only true for x>0, y < 1.5*x for x<0. I have not posted a
>> patch as I'm not sure what is the best way to change the example.
>>
>
> Seems a bit nit-picky, but we could change the example to
>
I would agree, it's nit-picky. But lots of people rely on the truth of
the documentation. People say I have any y/x > z situation, PostgreSQL
manual says it works better if I do y > z*x. So they do it, no
questions asked. Which is the real fear from my point of view.
> SELECT ... WHERE x > 0 AND y/x > 1.5;
> becomes
> SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
>
> regards, tom lane
>
> ---------------------------(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
>
>

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

http://archives.postgresql.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Minor mathematical error in documentation