Re: [PERFORM] Calculation of unused columns

2009-10-19 Thread Tom Lane
Simon Riggs writes: > On Mon, 2009-10-19 at 13:58 -0400, Tom Lane wrote: >> Most read-only functions are stable or even immutable. > Huh? I mean a function that only contains SELECTs. (How would those ever > be Stable or Immutable??) Uh, a function containing SELECTs is exactly the use-case for

Re: [PERFORM] Calculation of unused columns

2009-10-19 Thread Simon Riggs
On Mon, 2009-10-19 at 13:58 -0400, Tom Lane wrote: > > Most read-only functions are stable or even immutable. Huh? I mean a function that only contains SELECTs. (How would those ever be Stable or Immutable??) -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-performance mailing

Re: [PERFORM] Calculation of unused columns

2009-10-19 Thread Tom Lane
Gerhard Wiesinger writes: > Is the patch only for 8.5 or even backported to 8.4 and 8.3? That patch will *not* be backported. It hasn't even got through beta yet. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make ch

Re: [PERFORM] Calculation of unused columns

2009-10-19 Thread Tom Lane
Simon Riggs writes: > On Mon, 2009-10-19 at 13:43 -0400, Tom Lane wrote: >> Simon Riggs writes: >>> I think we should have a 4th class of functions, >>> volatile-without-side-effects (better name needed, obviously). >> >> What for? There wouldn't be that many, I think. random() and >> clock_ti

Re: [PERFORM] Calculation of unused columns

2009-10-19 Thread Gerhard Wiesinger
On Sun, 18 Oct 2009, Tom Lane wrote: Robert Haas writes: On Sun, Oct 18, 2009 at 1:59 PM, Jeff Janes wrote: Even if country.id is a primary or unique key? Well, we currently don't have any logic for making inferences based on unique constraints. Huh? http://archives.postgresql.org/pgsql

Re: [PERFORM] Calculation of unused columns

2009-10-19 Thread Simon Riggs
On Mon, 2009-10-19 at 13:43 -0400, Tom Lane wrote: > Simon Riggs writes: > > On Sat, 2009-10-17 at 21:41 -0400, Tom Lane wrote: > >> one thing we'd have to consider > >> is whether it is okay to suppress calculation of columns containing > >> volatile functions. > > > I think we should have a 4th

Re: [PERFORM] Calculation of unused columns

2009-10-19 Thread Kevin Grittner
Simon Riggs wrote: > I think we should have a 4th class of functions, > volatile-without-side-effects Sounds reasonable to me. > (better name needed, obviously). Well, from this list (which is where volatile points), mutable seems closest to OK, but I'm not sure I like any of them. http

Re: [PERFORM] Calculation of unused columns

2009-10-19 Thread Tom Lane
Simon Riggs writes: > On Sat, 2009-10-17 at 21:41 -0400, Tom Lane wrote: >> one thing we'd have to consider >> is whether it is okay to suppress calculation of columns containing >> volatile functions. > I think we should have a 4th class of functions, > volatile-without-side-effects (better name

Re: [PERFORM] Calculation of unused columns

2009-10-19 Thread Simon Riggs
On Sat, 2009-10-17 at 21:41 -0400, Tom Lane wrote: > one thing we'd have to consider > is whether it is okay to suppress calculation of columns containing > volatile functions. I think we should have a 4th class of functions, volatile-without-side-effects (better name needed, obviously). That wo

Re: [PERFORM] Calculation of unused columns

2009-10-19 Thread Tom Lane
I wrote: > Just for fun, I hacked together a first cut at this. Oh, just for the archives: I forgot about not suppressing volatile expressions --- checking that would increase the cost of this significantly, though it's only another line or two. regards, tom lane -- Sent

Re: [PERFORM] Calculation of unused columns

2009-10-18 Thread Tom Lane
Robert Haas writes: > It's probably true that in MOST of the cases where this comes up, the > subquery can be flattened, from_collapse_limit permitting. But I > think there are other cases, too. Right ... and from_collapse_limit is not relevant here; only the form of the subquery is. So I'd sur

Re: [PERFORM] Calculation of unused columns

2009-10-18 Thread Robert Haas
On Sun, Oct 18, 2009 at 4:54 PM, Tom Lane wrote: > Robert Haas writes: >> On Sun, Oct 18, 2009 at 1:59 PM, Jeff Janes wrote: >>> Even if country.id is a primary or unique key? > >> Well, we currently don't have any logic for making inferences based on >> unique constraints. > > Huh? > http://arc

Re: [PERFORM] Calculation of unused columns

2009-10-18 Thread Tom Lane
Robert Haas writes: > On Sun, Oct 18, 2009 at 1:59 PM, Jeff Janes wrote: >> Even if country.id is a primary or unique key? > Well, we currently don't have any logic for making inferences based on > unique constraints. Huh? http://archives.postgresql.org/pgsql-committers/2009-09/msg00159.php Ad

Re: [PERFORM] Calculation of unused columns

2009-10-18 Thread Robert Haas
On Sun, Oct 18, 2009 at 1:59 PM, Jeff Janes wrote: > On Sun, Oct 18, 2009 at 10:35 AM, Tim Landscheidt > wrote: >> Daniel Migowski wrote: >> >>> I have a very common example which would illustrate the >>> above problem a bit more. Guess the following view on a >>> company table, which references

Re: [PERFORM] Calculation of unused columns

2009-10-18 Thread Jeff Janes
On Sun, Oct 18, 2009 at 10:35 AM, Tim Landscheidt wrote: > Daniel Migowski wrote: > >> I have a very common example which would illustrate the >> above problem a bit more. Guess the following view on a >> company table, which references the country of that company >> in another table. The view it

Re: [PERFORM] Calculation of unused columns

2009-10-18 Thread Tim Landscheidt
Daniel Migowski wrote: > I have a very common example which would illustrate the > above problem a bit more. Guess the following view on a > company table, which references the country of that company > in another table. The view itself just returns the > company-id and the country-name, >cr

Re: [PERFORM] Calculation of unused columns

2009-10-18 Thread Tom Lane
Daniel Migowski writes: > I have a very common example which would illustrate the above problem a > bit more. This is (a) still handwaving, not a testable example, and (b) unrelated to the question at hand, because the suggested view is flattenable. regards, tom lane --

Re: [PERFORM] Calculation of unused columns

2009-10-18 Thread Daniel Migowski
I have a very common example which would illustrate the above problem a bit more. Guess the following view on a company table, which references the country of that company in another table. The view itself just returns the company-id and the country-name, create view companys_and_countries

Re: [PERFORM] Calculation of unused columns

2009-10-17 Thread Tom Lane
Robert Haas writes: > On Sat, Oct 17, 2009 at 9:41 PM, Tom Lane wrote: >> 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 r

Re: [PERFORM] Calculation of unused columns

2009-10-17 Thread Robert Haas
On Sat, Oct 17, 2009 at 9:41 PM, Tom Lane wrote: > 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

Re: [PERFORM] Calculation of unused columns

2009-10-17 Thread Tom Lane
[ please keep the list cc'd ] Volker Grabsch 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, >

Re: [PERFORM] Calculation of unused columns

2009-10-17 Thread Tom Lane
Volker Grabsch writes: > I'm confused about the absence of a very simple optimization > in PostgreSQL. Suppose we have a VIEW where some columns are > expensive to be calculated: > CREATE VIEW a AS > SELECT > (... expensive calculation ...) as expensive, > count(*) as chea

[PERFORM] Calculation of unused columns

2009-10-17 Thread Volker Grabsch
Dear PostgreSQL developers, I'm confused about the absence of a very simple optimization in PostgreSQL. Suppose we have a VIEW where some columns are expensive to be calculated: CREATE VIEW a AS SELECT (... expensive calculation ...) as expensive, count(*) as cheap FRO