Re: Calculation of unused columns - Mailing list pgsql-performance

From Tom Lane
Subject Re: Calculation of unused columns
Date
Msg-id 27954.1255830094@sss.pgh.pa.us
Whole thread Raw
In response to Calculation of unused columns  (Volker Grabsch <vog@notjusthosting.com>)
Responses Re: Calculation of unused columns  (Robert Haas <robertmhaas@gmail.com>)
Re: Calculation of unused columns  (Simon Riggs <simon@2ndQuadrant.com>)
List pgsql-performance
[ please keep the list cc'd ]

Volker Grabsch <vog@notjusthosting.com> writes:
> The "count(*)" in the example seems to be distracting. In fact,
> it could be replaced with a simple constant value, the effect
> is the same:

>     CREATE VIEW a AS
>     SELECT
>         (... expensive calculation ...) as expensive,
>         1 as cheap
>     FROM
>         x;

Well, if you try that case, you'll find that the "expensive" column
*does* get thrown away.  (Using EXPLAIN VERBOSE under 8.4 may help you
see what's going on here.)  It's only when there's some reason why the
view can't get flattened that there's an issue.

I've been thinking about this since your earlier mail, and I think it
would probably be possible to suppress unused columns in a non-flattened
subquery.  I remain unconvinced that it's worth the trouble though.
A real (not handwavy) example would help make the case here.

As an example of why I'm not convinced, one thing we'd have to consider
is whether it is okay to suppress calculation of columns containing
volatile functions.  I'd be inclined to think not, since that could
cause side-effects to not happen that the user might be expecting to
happen.  (We got beat up in the past for letting the planner be cavalier
about that consideration.)  I suspect, though, that that case and other
non-optimizable cases might account for the bulk of situations where the
existing optimization doesn't happen.

            regards, tom lane

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Calculation of unused columns
Next
From: Robert Haas
Date:
Subject: Re: Calculation of unused columns