pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExpression transformation curiosity
4+ messages / 3 participants
[nested] [flat]
* Expression transformation curiosity
@ 2003-08-17 10:32 Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 4+ messages in thread
From: Richard Huxton @ 2003-08-17 10:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Expression transformation curiosity
@ 2003-08-17 15:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-08-17 15:23 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Expression transformation curiosity
@ 2003-08-17 15:39 Alexander M. Pravking <fduch@antar.bryansk.ru>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 4+ messages in thread
From: Alexander M. Pravking @ 2003-08-17 15:39 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Expression transformation curiosity
@ 2003-08-17 17:01 Richard Huxton <dev@archonet.com>
parent: Alexander M. Pravking <fduch@antar.bryansk.ru>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2003-08-17 17:01 UTC (permalink / raw)
To: Alexander M. Pravking <fduch@antar.bryansk.ru>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-08-17 17:01 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-17 10:32 Expression transformation curiosity Richard Huxton <dev@archonet.com>
2003-08-17 15:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-17 15:39 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
2003-08-17 17:01 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox