On Wed, Feb 27, 2013 at 10:18 PM, Merlin Moncure <mmonc...@gmail.com> wrote:
> On Wed, Feb 27, 2013 at 8:22 PM, Chris Hanks > <christopher.m.ha...@gmail.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!
