Thread: Expression transformation curiosity

I just had a look at EXPLAIN ANALYSE output for an SQL function I'm trying to 
write. The WHERE clause wasn't too complex, but the explain output took up a 
dozen lines.

Boiling the problem down, I've looked at a clause of the form: a OR (b AND c)
which PG converts to: (a OR b) AND (a OR c)

Now these two are equivalent, but it would take me forever to demonstrate that 
with the full query. I'm happy the planner is going to get it right, but I'm 
confused as to why the transformation occurs.

Is it an artefact of displaying the EXPLAIN, or is it actually processed that 
way? You could see how testing "a" twice could be expensive in some 
situations.

Oh, btw - 7.3.2 on this box, I'll be looking at 7.3.3/4 later today.

--  Richard Huxton Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> Boiling the problem down, I've looked at a clause of the form:
>   a OR (b AND c)
> which PG converts to:
>   (a OR b) AND (a OR c)

> Is it an artefact of displaying the EXPLAIN, or is it actually
> processed that way? You could see how testing "a" twice could be
> expensive in some situations.

It's actually done that way --- see the comments near the head of
src/backend/optimizer/prep/prepqual.c.  There are some heuristics
to not do it if the expression expands "a lot", though.
        regards, tom lane

On Sun, Aug 17, 2003 at 11:32:58AM +0100, Richard Huxton wrote:
> I just had a look at EXPLAIN ANALYSE output for an SQL function I'm trying to 
> write. The WHERE clause wasn't too complex, but the explain output took up a 
> dozen lines.
> 
> Boiling the problem down, I've looked at a clause of the form:
>   a OR (b AND c)
> which PG converts to:
>   (a OR b) AND (a OR c)
> 
> Now these two are equivalent, but it would take me forever to demonstrate that 
> with the full query. I'm happy the planner is going to get it right, but I'm 
> confused as to why the transformation occurs.
> 
> Is it an artefact of displaying the EXPLAIN, or is it actually processed that 
> way? You could see how testing "a" twice could be expensive in some 
> situations.

Looks like it actually works this way.
I had the same problem several weeks ago on 7.3.3 with 4 such OR's.
The final filter became monsterous, and the query was very slow.

I've simply rewritten the query using UNION, and it became much faster.


-- 
Fduch M. Pravking

On Sunday 17 August 2003 16:39, Alexander M. Pravking wrote:
> On Sun, Aug 17, 2003 at 11:32:58AM +0100, Richard Huxton wrote:
> > Boiling the problem down, I've looked at a clause of the form:
> >   a OR (b AND c)
> > which PG converts to:
> >   (a OR b) AND (a OR c)

> > Is it an artefact of displaying the EXPLAIN, or is it actually processed
> > that way? You could see how testing "a" twice could be expensive in some
> > situations.
>
> Looks like it actually works this way.
> I had the same problem several weeks ago on 7.3.3 with 4 such OR's.
> The final filter became monsterous, and the query was very slow.
>
> I've simply rewritten the query using UNION, and it became much faster.

Doesn't seem to matter much in this case, although I tried it as a UNION just 
to check the results were the same.

Thanks Alexander
--  Richard Huxton Archonet Ltd