Re: View performance - Mailing list pgsql-performance

From Tom Lane
Subject Re: View performance
Date
Msg-id 4244.1040935555@sss.pgh.pa.us
Whole thread Raw
In response to Re: View performance  (Bruno Wolff III <bruno@wolff.to>)
List pgsql-performance
Bruno Wolff III <bruno@wolff.to> writes:
> I was naively expecting that the planner would notice the common
> subexpressions and only compute them once.

There isn't currently any code for detection of common subexpressions of
any kind.

My gut feeling is that searching for common subexpressions would be a
net waste of cycles in the vast majority of queries.  It'd be fairly
expensive (a naive implementation would be roughly O(N^2) in the number
of expression nodes), with zero payback in very many cases.

It might be worth doing for very constrained classes of subexpressions.
For instance, I was just thinking about putting in some code to
recognize duplicate aggregates (eg, "sum(foo)" appearing twice in the
same query).  nodeAgg.c could do this relatively cheaply, since it has
to make a list of the aggregate expressions to be computed, anyway.
I'm not sure about recognizing duplicated sub-SELECT expressions; it
could possibly be done but some thought would have to be given to
preserving semantics.

            regards, tom lane

pgsql-performance by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: View performance
Next
From: Tom Lane
Date:
Subject: Re: View performance