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

On Wed, Feb 27, 2013 at 10:18 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> 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
>

What would that look like? I've googled around for an example of what
you're talking about, but I'm not finding anything. I think I know how to
write a SQL function that will return a set of rows given a fkey1 value,
but I don't see how I'd turn that into a view...?

Thanks!