Re: Poor performance when using a window function in a view

On Wed, Feb 27, 2013 at 8:22 PM, Chris Hanks
<christopher(dot)m(dot)hanks(at)gmail(dot)com> 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 reproduction of my situation
> with 9.2.2:
>
> ---
>
> drop table if exists values cascade;
>
> create table values (
> fkey1 integer not null,
> fkey2 integer not null,
> fkey3 integer not null,
> value float not null,
> constraint values_pkey primary key (fkey1, fkey2, fkey3)
> );
>
> -- This is kind of hacky, but it roughly resembles the size and distribution
> of my dataset.
> insert into values select distinct on (fkey1, fkey2, fkey3)
> i / 12 + 1 as fkey1,
> i % 4 + 1 as fkey2,
> ceil(random() * 10) as fkey3,
> random() * 2 - 1 as value from generate_series(0, 199999) i;
>
> 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) then 4 else 1 end)) as double
> precision) as derived1,
> sum((case when (value > 0.0) then (value * 4) else (value + 1) end)) as
> derived2
> from values
> group by fkey1, fkey3
> ) as t1;
>
> -- This query requires a sequential scan on values, though all the data it
> needs could be found much more efficiently with an index scan.
> explain analyze select * from values_view where fkey1 = 1263;
>
> ---
>
> Can anyone suggest a way to rewrite this query? Or if postgres isn't capable
> of optimizing this right now, is there a workaround of some kind? This is a
> view I'd like to be able to join a smaller table against.

this comes up a lot. only way to expose as a view is to push the query
into a set returning function which you then wrap into a view.
downside is that any query except on fkey1/fkey 2 will have to fully
materialize view.

merlin