Re: [GENERAL] Poor performance when using a window function in a view

2013-03-01 Thread Chris Hanks
On Fri, Mar 1, 2013 at 6:37 AM, Merlin Moncure wrote: > On Fri, Mar 1, 2013 at 3:59 AM, Chris Hanks > wrote: > > On Fri, Mar 1, 2013 at 1:21 AM, Raymond O'Donnell wrote: > >> > >> On 01/03/2013 00:19, Chris Hanks wrote: > >> > On Thu, Feb 28, 2013 at 6:00 AM, Tom Lane >> >

Re: [GENERAL] Poor performance when using a window function in a view

2013-03-01 Thread Misa Simic
Hi Chris, You don't need to make a a full view - to join it later to "less rows number table") If you have, function what takes fkey1 as input parameter and returns SET OF (type of your values_view) i.e. CREATE OR REPLACE FUNCTION get_filtered_values_view(in_fkey1 integer) RETURNS SETOF value

Re: [GENERAL] Poor performance when using a window function in a view

2013-03-01 Thread Merlin Moncure
On Fri, Mar 1, 2013 at 3:59 AM, Chris Hanks wrote: > On Fri, Mar 1, 2013 at 1:21 AM, Raymond O'Donnell wrote: >> >> On 01/03/2013 00:19, Chris Hanks wrote: >> > On Thu, Feb 28, 2013 at 6:00 AM, Tom Lane > > > wrote: >> > >> > Chris Hanks > >

Re: [GENERAL] Poor performance when using a window function in a view

2013-03-01 Thread Chris Hanks
On Fri, Mar 1, 2013 at 1:21 AM, Raymond O'Donnell wrote: > On 01/03/2013 00:19, Chris Hanks wrote: > > On Thu, Feb 28, 2013 at 6:00 AM, Tom Lane > > wrote: > > > > Chris Hanks > > writes: > > > create or replace view v

Re: [GENERAL] Poor performance when using a window function in a view

2013-03-01 Thread Raymond O'Donnell
On 01/03/2013 00:19, Chris Hanks wrote: > On Thu, Feb 28, 2013 at 6:00 AM, Tom Lane > wrote: > > Chris Hanks > writes: > > create or replace view values_view as > > select fkey1, fkey3, > > (derived1 / max(der

Re: [GENERAL] Poor performance when using a window function in a view

2013-02-28 Thread Chris Hanks
On Thu, Feb 28, 2013 at 6:00 AM, Tom Lane wrote: > Chris Hanks writes: > > create or replace view values_view as > > select fkey1, fkey3, > > (derived1 / max(derived1) over (partition by fkey1)) as derived1, > > (derived2 / sum(derived1) over (partition by fkey1)) as derived2 > > from ( > >

Re: [GENERAL] Poor performance when using a window function in a view

2013-02-28 Thread Tom Lane
Chris Hanks writes: > create or replace view values_view as > select fkey1, fkey3, > (derived1 / max(derived1) over (partition by fkey1)) as derived1, > (derived2 / sum(derived1) over (partition by fkey1)) as derived2 > from ( > select fkey1, fkey3, > cast(sum((case when (value > 0.0) th

Re: [GENERAL] Poor performance when using a window function in a view

2013-02-28 Thread Chris Hanks
On Wed, Feb 27, 2013 at 10:18 PM, Merlin Moncure wrote: > On Wed, Feb 27, 2013 at 8:22 PM, Chris Hanks > wrote: > > Hi. Sorry if this is repetitive, I tried posting to pgsql-performance > first > > but I think it got stuck in moderation. > > > > I'm trying to create a view that uses a window fun

Re: [GENERAL] Poor performance when using a window function in a view

2013-02-27 Thread Merlin Moncure
On Wed, Feb 27, 2013 at 8:22 PM, Chris Hanks wrote: > Hi. Sorry if this is repetitive, I tried posting to pgsql-performance first > but I think it got stuck in moderation. > > I'm trying to create a view that uses a window function, but it seems that > Postgres is unable to optimize it. Here's a r

[GENERAL] Poor performance when using a window function in a view

2013-02-27 Thread Chris Hanks
Hi. Sorry if this is repetitive, I tried posting to pgsql-performance first but I think it got stuck in moderation. I'm trying to create a view that uses a window function, but it seems that Postgres is unable to optimize it. Here's a reproduction of my situation with 9.2.2: --- drop table if ex